[MUSIC PLAYING]
Welcome to MSPTDA video number 13.
Now, the last 12 videos we've been studying--
Power Query.
And now we want to study Power Pivot.
Now, we're going to have three introductory videos.
And I want to start off with talking about video number 3--
Power Pivot Intro #3.
We'll have a comprehensive introduction
to Excel Power Pivot--
Data Model, DAX, and the power of Power Pivot.
But, before we get to this comprehensive introduction,
I got to show you two awesome tricks for Excel, where
we use Power Pivot not to its full extent,
but just to help us in Excel.
Video number 2, we'll see how to use Power Pivot to import
3 million rows into Excel.
And in this video, we'll see how to use the Relationship
feature to replace VLOOKUP.
Now look at this.
There's a big red line through VLOOKUP.
That doesn't mean that in Excel, we
don't have hundreds of awesome uses for VLOOKUP.
But when we use VLOOKUP in a helper column attached
to a proper data set that we're going
to use as source data for a pivot table,
we do not want to use VLOOKUP.
Now let's go look at what I mean.
Here's an example of the revenue table.
Here's the lookup for country and lookup for product.
We want to avoid having VLOOKUPs look something up and retrieve
it.
Now, this VLOOKUP is fine on a small data set.
But Control-Down Arrow-- we have 73,000 rows here.
So that's a lot of formulas.
And we had to do it twice.
So instead of using VLOOKUP, we can use relationships.
Now, if you've never done a relationship before,
it's the same as VLOOKUP.
F2-- what's this formula doing?
It's looking up product in this table.
But, remember, the first column of any lookup table
has a unique list of items.
That means there's exactly one of each item.
But over here in this column, of course,
we can have multiple listings of products.
Anytime you have this situation--
one to many-- lookup table over to a fact table or a sales
table, we can use the Relationship feature
rather than VLOOKUP.
Now, the relationship feature is only one part of Power Pivot.
And before we jump over to Excel,
I want to talk about what is Power Pivot.
And this will be a basic answer.
In intro video number 3, we'll cover it in more detail.
First off, Power Pivot automatically
comes in Office 365.
It has been around in earlier versions as far back as 2010,
but it was either an add-in, or you
had to buy the correct version of Excel.
And sometimes that was difficult.
Now, Excel Power Pivot provides three data tools.
The columnar database is amazing.
It's a behind-the-scenes in RAM memory, efficient, big data
analytics database.
And we'll talk more about, exactly,
the structure of that columnar database two videos ahead.
But it's what allows us to hold big data
and have a small file size.
Relationships between tables is another feature of Power Pivot.
And then DAX formulas.
These are formulas we get to create and use in pivot tables.
Now, we don't get to see DAX formulas in this video,
but two videos ahead, we'll learn DAX for the first time.
Now, columnar database-- that's what holds the big data.
Relationships between tables, so we
don't have to use VLOOKUP and DAX formulas--
together those three items make up the Data Model.
So when you hear the word data model,
you think columnar database.
That's our big data relationships and DAX formulas.
Now, from the Data Model, we make pivot tables.
And to distinguish between standard pivot tables and Data
Model pivot tables--
I'll always refer to them as Data Model pivot tables.
Now, the basic advantages of using Excel Power Pivot
is that we can work on millions of rows of data.
We can now use relationships between tables
and have multiple tables in our pivot table field lists.
And we can use DAX formulas.
DAX formulas, as we'll see a couple of videos ahead,
provide more variety than a standard pivot table.
And, of course, they are specifically
designed to efficiently work on big data.
Now let's go over to Excel.
Now, we're over here in our Start file,
and we have the same 1, 2, 3 tables.
Now, in order to get data from an Excel sheet into Power Pivot
or into Power Query, like we've been doing the last 10 videos,
the data has to be in an Excel table.
So I've already converted these two tables.
I'm going to click on the product table--
Control-T-- to convert it to an Excel table and enter.
I want to make sure and name it--
Table Tools Design-- over to Properties.
And I'm going to name this Product Lookup Table and enter.
Now, guess what?
This video is only going to show us
how to use relationships to replace VLOOKUP,
but there's actually two other important things
that we want to learn in this video.
One is when we do this, if you were
to compare files size to two VLOOKUP columns
or using relationships, the file size is much smaller.
Also, this trick works no matter what version
you have, either Excel 2013 16 or Office 365,
because guess what.
I'm going to start off pretending like I don't even
have Power Pivot because over in the Data ribbon tab--
Data Tools-- there's the Relationship button.
This is actually a secret backdoor way
to build relationships and put these tables into the Data
Model, which is a much more efficient place to store data.
So here's how we do it.
I'm going to, in the Data Tools, click Relationship.
New.
Now, we have to take a look at this.
It says Table and Related Table.
Table is always going to be our Factor Sales table.
Related Table is always going to be the Lookup table.
Now, there's a hint over here for database people.
And for the prerequisites for this class,
we know the difference between Primary key and Foreign key.
Primary key means that's the first column of the Lookup
table.
You have to have a unique list.
Foreign means that same key, but we can have many repeats.
One too many.
So we're going to select.
And notice that it says worksheet
for each one of these.
There's our Sales table, so I'm going to select that.
We're first going to build a relationship to the Product
table, so that's the Foreign key.
We're going to select.
And notice it says Worksheet Table Product Table and then
the Primary key.
Now, I'm going to click OK.
And when we come back to our Manage Relationships dialog
box, there's the relationship--
Sales table, Fact table, many side, Foreign key.
There's the Lookup table, or the Dimension table.
First column has a unique list.
This is the one side, or the primary key side.
Now let's create our second relationship.
And watch what happens when I click New--
then go to the Table dropdown.
Look at that.
It tells us from this dropdown--
Data Model Product Table, Data Model Sales Table.
Even though we don't have the official Power Pivot add-in,
it still allows us, through relationships, to add tables
to the Data Model.
Now we're going to create our second relationship, sales.
And this is going to be on Country Code.
Related Table or Lookup Table or Dimension Table--
that's going to be--
and look.
It has not been added to the Data Model yet.
Worksheet Table Country I select and then primary key, one side,
first column of the Lookup table, country code.
Now when I click OK, I have my two relationships.
Now we're going to close this.
And there's a relationship between our Sales,
or Fact, table, and these two look up our Dimension tables.
The tables and the relationships are stored in the Data Model.
Now, we'll go look at the Data Model in just a second,
but, remember, this trick is assuming
that we do not have the correct version for Power Pivot.
And, in fact, we haven't even added
that Power Pivot ribbon tab.
So we're going to continue assuming
we don't have Power Pivot.
Insert pivot table or use the keyboard--
ALT-N-V. And look at that.
Even though we don't have the right version,
the Create Pivot Table dialog box
knows that it's behind the scenes
and assumes we want to use it.
I'm clicking OK.
Here's our Pivot table.
Here's our Pivot Table fields.
And there are four tables.
Well, there's our Country Table Product Sales,
but where did Daily Sales and Employee Cost come from?
Well, guess what?
Anytime we invoke a Pivot table, it's
going to show us all of the different Excel tables
or Data Model tables in our Pivot Table field list.
If we go over to Extra Tables, here's one table.
This table came from a Power Query import,
so they all show up.
Now, hopefully, you know the name of your tables.
That's an easy way to tell.
And since we're using Office 365, all of these icons
are the same.
In earlier versions-- Excel 2016 and 13--
only the Data Model Pivot tables had a dark line across the top.
All of the other Excel tables from the Excel workbook
did not have a dark line.
So we can't tell by the icon in Office 365, but look at this.
If I hover my cursor, it tells me
information about this table.
It says Model Table Name, Model Table Name, Model Table Name.
Here it doesn't say the model.
It just says the data source.
And down here it just tells us data source.
So that's another way.
Now, what we want to do is we want to move them to Active.
So I'm going to right-click Show in Active.
Right-click-- right-click Show in Active.
Now we can go over to Active and look at that,
pull down the middle part.
And there's our Data Model.
We are allowed to pull fields from any one of these tables.
We're going to drag Country from Country Lookup down to Rows.
Instantly, we get a unique list.
Now we're going to drag Product down below Country.
There's a unique list of products.
Now we're going to drag a Number field from Revenue.
When I click and drag down to the Values area,
it creates our calculation with two conditions or criteria.
That number right there is the total for [? Bauer ?]
[? Aussie ?] [? Round ?] in Algeria.
Now, something very important about what we did here.
We, in essence, are using relationships,
which are part of Power Pivot and the Data Model
just as a substitute for VLOOKUP.
When we drag a Number field down here,
it looks like a standard pivot table calculation.
But, as we'll learn two videos ahead,
this is actually a DAX formula.
It's an implicit measure.
Now, when we get to building full data models,
we are not going to use implicit measures.
But if you're simply using the Relationship feature to connect
multiple tables that you have in Excel
to build some pivot tables--
perfectly all right to drag and drop a Number field
and have what looks like a standard pivot table
calculations.
Now, remember, Microsoft did this for a reason.
For casual uses of relationships,
Power Pivot in the Data Model--
they want to accommodate people's ability to simply make
a pivot table quick and easy.
Now, one last thing we want to do
is we want to actually show the Power Pivot ribbon tab
and go look at the Data Model.
File, down to Options, down to Add-ins, Manage.
Dropdown, and we want COM Add-in.
Go.
And if you have Power Pivot in earlier versions-- or, for us,
we have Office 365, so Power Pivot's automatically
in our Excel--
you still have to come here, check the COM Add-in, click OK.
And now we have our Power Pivot ribbon tab.
If we click Manage Data Model in Power Pivot ribbon tab
or over in Data, Manage Data Model,
it'll open the Power Pivot for Excel window.
This allows us to look at the Data Model.
And, sure enough, here's the tables.
Now, these tables are not stored like this.
This is just our picture of the Data Model.
Remember, there's a columnar database
behind the scenes that stores things efficiently.
We want to go over to View, Diagram View.
And I already moved these tables around.
Usually, they don't come as neat and tidy as this.
But there it is--
Factor sales tables and our two Lookup, or Dimension, tables.
One too many.
One too many relationship.
Now I'm going to close this.
Two videos ahead, we'll deal with this window
and do lots of cool things with Power Pivot.
I'm going to close.
All right.
So in this video, Data ribbon tab-- that button right
there is awesome.
When you want to replace VLOOKUP using relationships,
there it is.
All right.
If you like that video, be sure to click that thumbs up,
leave a comment, and subscribe because there's always
lots more videos to come from Excel Is Fun, including
video number 14 in this class.
Power Pivot Intro #2, next video,
we'll see how to do another Excel Power Pivot trick.
All we're going to do is use the amazing Power Pivot Data Model
along with Power Query to help us import and store millions
of rows of text data in Excel.
All right.
We'll see you next video.