Welcome to Highline Excel
2016, video number 14. If you like to
download this workbook, Busn218-Video12-14Start
file, and follow along, click on the link
below the video. Hey, we have a great video here. We actually want to compare
VLOOKUP and relationships in the data model. The last couple videos have
been talking all about LOOKUP. Back in video number 3, we had
an introduction to the data model in PowerPivot. Now, here's a situation
where you have date, sales rep, and sales. And over here we
have a lookup table with sales rep and region. And we need to,
from both , tables, create a regional sales report. Now, for VLOOKUP, we can
simply add a helper column and look up region. For relationships
in the data model, we'd add both of these
tables to the data model and then build a relationship
between the unique list in the first column of the
lookup table for sales rep, and the Sales Rep column over
here that has many duplicates. Now, when would you use
VLOOKUP and when would you use relationships
in the data model? Well, if your data
is not big data, and you're doing a simple
report where you have one or two lookup columns,
then by all means, it's faster to simply add this
helper column with VLOOKUP. If you actually have big data or
you're building a complex data model with relationships
between many tables, then it's better to use the
data model in PowerPivot. Also, a couple weeks ahead when
we learn how to tax formulas, we'll see there are some other
advantages for using the data model. But in general, if
you have a table, it could have thousands or
even tens of thousands of rows. If it's a simple
extra column or two used in VLOOKUP,
then no problem. Now, I actually want to
do both examples here. And that sheet, I
want to copy it over. So instead of right-clicking
and pointing to Move and Copy, I want to show you a
cool copy sheet trick. Now watch this. I'm going to click on
the sheet and drag up. You can see the piece of
paper under my cursor. And the little black
arrow means I'm going to drop it right here. Actually, it means
I'm going to move it. But watch this. I'm going to hold
the Control key, and look what happened
to my piece of paper. There is a plus there. That means I'm not
moving it, I'm copying. Now, the trick is you have
to let go of your mouse, not the Control key, to get
it to actually copy over. Now, I'm going to
double-click and call this DM for data model. Now I want to come over to 14. And let's create the
helper column first. So I'm going to
call this Region. Enter. =VL, Tab. I'm going to look
up the Sales Rep. Notice that's table
formula nomenclature with an @ symbol,
which means that's a relative cell reference. Comma. And the table, since
I'm highlighting all the records in the table,
it gives me exactly the table name. Comma. Region is the second
column in the lookup table. So I have to put a two
here to tell VLOOKUP to please look up Region. Comma. And I'm going to put
0 for exact match because I'm not sure if the
Sales Rep column will always be sorted. Control-Enter and
double-click and send it down. Now, most of the time if
you're using the Excel table feature, when you enter that
formula, it automatically gets sent down. Go to the last cell, F2. And there we go. Now I can click
in a single cell. Insert Pivot Table or
the keyboard Alt-N-V. And I'm going to put it
somewhere like over in K3. Click OK. Now I can simply
drag Region, this is my helper column that
contains our VLOOKUP formula. I'm going to drag
it down to rows. And instantly I
get a unique list. Now I drag Sales. And boom, there we go. Immediately up to Design,
Report Layout, Show In Tabular. Right-click. Number Formatting. And Currency,
something like that. Zero decimals is fine. Click OK. And so there it is. We created a pivot table
report for a region using VLOOKUP and a helper column. Now we want to go
over to the DM and see how to do this
using relationships in the data model. Now, when I copied the sheet
over, that table had a name and it automatically changed it. I do not want to rely on that. So I'm going to go up to Design. And up here it said fSales9. I'm going to get rid of
the 9 and call it fSalesDM. Enter. Now I click in a
single cell over here. Alt-J-T-A is the name
to shoot me up here. And I'm going to remove
the 10 and put dSalesRepDM for data model, and Enter. Now, in order to put these
into the PowerPivot data model, they have to be Excel tables. So now we have these as table. I'm going to click
in a single cell and click add to data model. And there it is. There is our table number one. Alt-Tab. I click in the second table. Add To Data Model. Now I have my two tables. I can go up to the View
Group Diagram View. And when I click
Diagram View, now I have my two field lists
for each one of the tables. Hey. Here's the first column
in our lookup table. I'm going to drag Sales Rep
over to Sales Rep in the Sales table. Instantly I see a one-to-many. That one means there is a
unique list in the first column of the lookup table. And over here, that
asterisk means many. That means I can have
many repeats over here. There is a one-to-many
relationship. Now I'm going to come over
and click the Pivot Table. New Worksheet is fine. Click OK. I'm going to immediately going
to double-click this and call it PT-DM, Pivot Table
from the Data Model. Now, notice in the
field list the tables with the black line
at the top mean they are in the data model. There's an All and an Active. I'm going to move
these to the active. Right-click, Show Inactive. Right-click, Show Inactive. Now I go over to Active. And look at that, two tables. I am totally allowed to drag
Region from Sales Rep table down the rows. No VLOOKUP in that column. That's from a
relationship, right? And then Sales over to Values. And instantly, there we go. Click in the cell. Design, Report Layout,
Show In Tabular. Right-click, Number Formatting
to format the actual field. And something like
Currency, zero. By the way, when we study DAX
formulas in the data model two weeks ahead, we'll
see that we never have to use number formatting. Because when we create our
DAX formula in the data model, we'll actually attach number
formatting to the formula. Hey, there we go. There's the same report,
but we use relationship between two tables. Alt-Tab. There's our two tables
in the data model. And we did VLOOKUP
in a helper column. All right. One important thing to
take away from this. We want to be able to think
of VLOOKUP as a relationship in the data model. But we also want to think
when we're doing relationships in the data model, think of
those relationships as VLOOKUP. For many of us coming from Excel
to relational type database and stuff and the
data model, we already think of things as VLOOKUP. So either way you
do it, they're both accomplishing the same goal. All right. Next video will start talking
about EXCEL charts all right. See you next video.
