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.