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

[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.

top

Thanks mike girvin excelbrain 🙂

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

Thanks Mike

Great as always. Thanks Mike

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

I'm glad they put PowerPivot back in Office365 Personal!

Nice

Thanks for another great video, it's been a great series. Thank you Mike!

You GIVE and you are LIMITLESS, I have learn so much, and I am forever grateful to YOU, THANKS

Info is really helpful.

Hello Mike, the link for the file isn't going through.

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….

Literally the only person I watch for excel videos!! Always appreciate the enthusiasm in your videos!

Wonderful vedio..

Nice beginning to PP, Mike. We just

knowthis 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!…

As always thanks for the great video

Thanks Mike. Power Pivot videos, here we go 🙂 🙂

Awesome Mike !!!!!!

Another great video!!

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.

Excelente!! who shares what he knows reveals his true essence..muchas gracias por compartir!!

Great as usual :). Thanks Mike

Well done, Mike !!! Thanks:-)))

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

EXCELlent video Mike. Thanks for sharing

I have Office 365 (Up-to-date, afaik) and still have different icons for Data Model Pivot Tables…

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.

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

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!

Thanks for relationship-power-pivot fun 🙂

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

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!

Excellent video.

Power Pivot….I put that s^!t on everything… Thanks Mike

Quick Refresh 🙂

Thanks for sharing. Can i use your data for my Video, please?

very good

Thanks a lot for your exellent sharing! <3

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.

I am learning so much from you. Thank you for making this so enjoyable to learn.

AN AWESOME THUMB AND MANY LIKES FOR YOUR WONDERFUL VIDEOS

Thank you very much for everything you doing for us!!!

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

Is it better to make relationships with power query or with power pivot data model, if you have big data?

Awesome video as always! Every video I learn something amazing from you.