Welcome to Highline BI
348, class video number 39. Hey, if you want to
download this workbook, BI 348, chapter 2.5, import 9. And be sure to
download the text file that we're going to import
and the regional table from an Excel file that
we're going to import. Now let's go look
at those two files. Here they are. This one is the
transaction table. And I want to double
click and open it up. And this one is
the Lookup table. And I want to open this up. Here's the two files. Now this is the
transactional data. We can see date, sales rep,
product, discount units, and net revenue. I do not see a region column. And our ultimate goal
here is to add up all of the net revenue
for each region. Now what would we
normally do in Excel? If we had both of these tables
and they weren't gigantic data sets, we would just add
a VLOOKUP helper column. And notice, we are going to look
up-- it's say sales rep-- we're going to look up each
one of these sales reps over in the Lookup table. Now remember, the characteristic
of a Lookup table, this first column,
Control down arrow, so there's like 72 sales reps. But this first column
is a unique list. There's exactly one
listing of each sales rep. And then it lists the region
that they're responsible for. Now in this video, we want to
see how to import both of these and replace VLOOKUP. Now VLOOKUP is fine when
you have the data in Excel and it's not a big data set. You just add that extra column. But if we're importing
it, and there's some other options
available to us, especially if you
have big data, where an extra column with
hundreds of thousands of rows would really add some
extra expense in terms of formula calculating, then
these two methods are awesome. And the two methods
are going to be, one, we're going to import both
tables into the data model. And in 2013 or later, when we
open the Create the Pivot Table dialogue box, both
tables will be there. And we'll be able to
add a relationship. The relationship will be
between Sales Rep column in the Lookup table and sales
rep in the Sales Transaction table. The second method we'll
be import both tables into Power Query. And then merge them. Now Power Query
uses the word Merge to mean you have to have a
column in each table that are related. Sales rep is related. And when we click Merge, it'll
automatically create the Helper column to simulate VLOOKUP. Now I'm going to close
both of these file. Now we go to a Power
Query, From File. And the first one we're
going to import is from Text. Now I'm navigating to where I've
downloaded this import 9 sales table. Double click. It's a text file,
so it pretty much should interpret it correctly. It imported headers and
changed the data types. We could quickly
check each data type to see that, sure enough,
it got all of them correct. We can simply Close and Load to. We're not doing a table. We're going to say Only
create a connection. And then come down and say
Add this to the data model. And click Load. So we have 10,000
records loaded there. Now we need to get
our Lookup table. Power Query, From
File, From Excel. There it is. Double click to import. In our Navigator pane,
we select Region table. I'm definitely going to
come down to the bottom and click Edit. I need to promote these headers. So I come up to the
upper left-hand corner and say Use first
row as headers. I can check the data type
for each one of these. Looking good. Now I can Close and Load to. Not a table, Only
create a connection. And down to Add this to
the data model, and Load. Now both of those are
in the data model. I can close this. And guess what? I can create a pivot table. Insert, Pivot Table, or Alt N V. Use external data source,
Choose a connection, and Tables. And unlike earlier when
we accessed the tables from the Create, Pivot
Table dialogue box, we have only one table
in earlier videos. Here, we have two tables. So I'm going to click
on Data model and Open. Click OK. And sure enough, look at that. In the Pivot Table Field
list, we have two tables. Now we're going to
run into a problem, but it will only be temporary. Now what I want is
from the Region table, I want to drag
Region down to Rows. And instantly, I
get a unique list. Now I come up and my goal
is to get that Net Revenue. So I draw drag it
down to Values. And whoa, it's the same
number all the way down, which is actually the total. And the reason why is
there is no relationship between the sales rep
table where we have region and the transaction table. But no problem. Over here, if we try
to drag and drop fields from multiple tables and
there's not a relationship, this will come up. Now I'm going to click
the Create button. But I also want you
to note that next week when we do Power Pivot, we
won't have to do this step here. We will actually
automatically be able to create relationships
between the tables. However, we won't rely on the
Pivot Table user interface. But if you don't
have Power Pivot, we can still pull data from
two tables by clicking Create. And the trick is that
the related table is the lookup table. And one way to remember is that
if you have a lookup table, that first column has
to have a unique list. And if you remember
from our study of Access all the way back in Business
216, the first column of a table with a
primary key-- well, it's called primary
because a primary key means a unique list. So that's the way I remember. It says, primary. I'm like, oh, yeah, that's the
unique list in the first column of the lookup table. So I'm going to
click the drop down. And this one has to
be that Region table. That's the Lookup table. Then we select the
field sales rep. Now we come up here to get
our Transaction table, which is the Sales table. And it will automatically
populate sales rep from that column,
because remember if you're doing VLOOKUP, we're
looking up the sales rep here, going and finding a match in the
unique list in the Lookup table and going to return
to the region. So when I click OK,
that is amazing. Instantly, because there's a
relationship between these two tables, I'm allowed to drag and
drop fields from both tables. Design. Report Layout, Show in
tabular, right click. Number Formatting, Currency. I'm going to keep
it as two decimals. And click OK. So that's one way to import
two tables into the data model, create a relationship, and be
able to drag and drop fields from both tables. Now I want to actually
go over to a new sheet, create a new sheet, and call
this RR for regional report. And I'm going to call this
Regional Report Power Query Merge. And actually I'm gong to come
over here and properly name this one. And I called it Regional Report
Data Model Relationship-- it's not long enough. I can't type
relationship-- so we know that both of
these reports will be exactly the same,
except for we will simulate VLOOKUP in different ways. All right, let's
do Power Querying. Guess what? If we had not already
imported both tables, we would have to do that step. Get the one from text. Import it as a connection only. Import it from Excel. Import it as a connection only. Now in both cases,
if I were to do that and I wasn't using
the data model, I would not check
the Data Model. But watch this. If we go up to Power
Query now, and Show Pane, we can see that there are two
connections there, 1 and 2. So we can go up to
Power Query and Merge. The first one's going to
be our Transaction table, or our Fact table. And I'm going to
select the Sales table. And there it is. The second one is going to
be our Lookup table, Region. And notice sales rep, sales
rep, you simply click. I'm not even going
to hold Control. I'm going to click
on the second one. And down here, it
even tells you that it found, in essence
due to VLOOKUP, and found a match
in all of those. When I click OK, what's going to
happen is it's going to ask me which column do I want to add? And we're going to say Region. And just as if we
had done VLOOKUP, it'll magically add the
column to this first table. So when I click OK,
we'll see the Editor. Notice, it says Table
because that sales rep table has multiple columns. I'm going to click
this Expand button. And we're going to use it
a slightly different way than we had in earlier videos. I'm going to uncheck everything. And the only one I
want here is Region. And when I click OK, I
have simulated VLOOKUP. Now I can double click
the Column header and call this Region, Enter. I'm checking the
data types for each. That's looking good. And finally, date. Now when we close
and load this, I can choose to load it as
a table in the workbook or as a connection and then use
the pivot table to access it. But I'm going to load this
one to the actual worksheet as a table on the
existing worksheet. A1, Click Load. Now immediately
some of you should be screaming, especially if
you watch a lot of my videos, because I just violated
a very important rule. In an efficient
computer use, I forgot to give this a good name. So I'm going to right
click Properties. And I'm going to call
this-- I always give it a ridiculously long
name that tells me exactly what I did here. Import Sales and Lookup Tables
and Merged into one Table, click OK. That's a much better name. Now I can click over here. Alt N V. And I'm going to put
the pivot table on this sheet here, something
like I1, click OK. Scroll over and this
one will be easier. Region down the Rows,
revenue down to Values, Design Report Layout, Tabular,
right click, Number Formatting. And we'll add something
like currency. Click OK. Wow, look at that. We got the same exact
pivot table report, pulling fields from two
different tables when we use Power Query
and Merge, as we got when we did data
model and relationships. All right, so in
this video, we saw how to take fields from
two different tables and make a report using
two different methods. Hey, this is the last
video for chapter 2.5. And what do we do
in this chapter? We learn how to import,
clean, and transform data. We got our hands messy you
with data which is really what you have to do out
there in the working world before you use that data
to create reports and do analytics. Hey, next video,
we'll get to see the basics of our new add-in
for building data models and creating reports
called Power Pivot. All right, we'll
see you next video.
