MSPTDA 13: Power Pivot Introduction #1: Relationships rather than VLOOKUP for PivotTable Report

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.

Michael Martin

46 Responses

  1. I have nothing to say but u are always the best and u know it thanks a lot for helping me change my life and save my company tons of times

  2. Thank you Mike. I thought I already knew the basics on powerpivot and you prove me wrong yet again. You are an invaluable resource.

  3. Awesomee….. So we r soon hitting the DAX….love to follow every bit of it..

    Lately gil raviv book on power query is out… Have u seen that mike….

  4. Nice beginning to PP, Mike. We just know this is gonna be great…
    On a side note: are you aware of the brand spanking new Excel functions called “Dynamic Array” functions that use the ‘spill’ feature?
    That really looks awesome!…

  5. Relationships are great! Makes it all so simple to setup. Thank you Mike for another great video. Looking forward to the next ones in this series.

  6. Your channel is one of the most valuable ones on YouTube. Much respect and thanks for sharing your knowledge! Kudos to you, sir.

  7. Thank you, Mike. An excellent addition to a strong series. (Yes, I know, I always say that. But what to do, when you just keep the niveau , Mike.) Power Pivot & Query are solid advanced material and set your mind to working with numbers and data.

  8. Oh well, here we go again, the Mac version doesn't seem to have the power pivot and relationship tool. I really have to switch to PC for sure this time. there so many cool features on excel that are not available on Mac. Why

  9. Thanks Mike. I was about to use some VLOOKUP formulas in a large spreadsheet but your video reminded me of the better way to do it. Your videos are always helpful!

  10. Dear sir, Can I get the date wise stock report requirements

    Such as opening,purchase, sale and then closing

    Next day to carry forward the closing as opening
    If new purchases match with opening then unique them and new with vba/formulated base.

    Please revert as early

  11. Hi Mike.. another great video. Excel can do so many awesome things, it's hard to pick an emphasis or focus. Your excellent videos and playlists along with your clear and concise instructions, keep me on track, learning something new every day. Thanks and Thumbs up!

  12. Brilliant. I wish I could be as good as you are in Excel, Mike. Many many thanks for your valuable efforts. I highly admire your unlimited knowledge in this software. All the best !!! You are EXCELLENT.

  13. Bloody Windows 365. It didn't have the term relationship. Just an icon that represents relationship.

Leave a Reply

Your email address will not be published. Required fields are marked *

Post comment