This episode shows you how to manually define a relationship between two tables in PowerPivot.
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
PowerPivot for the Data Analyst 5: Defining Relationships.
Well, hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Now, in this episode, I want to talk about manually defining a relationship. Now, it's possible to go ahead and create the pivot table and let Excel try and auto-detect the relationship.
But, if you're a better-safe-than-sorry kind of person, let's just define the relationship; and, boy, is it easy to define this relationship.
So, let's take a look at our data.
Here's my 1.8 million row data set.
You see I have a field called "StoreID".
And, then, here's my 144 row lookup table-- it has a field called "Store".
And there's a one-to-one relationship for each StoreID on the 1.8 million rows-- there'll be a corresponding store here-- and, of course, you know, before PowerPivot, this would be a lot of VLOOKUPs to join this data together.
1.8 million rows of VLOOKUPs for every single field, here, would take forever to calculate.
Watch how easy this is to do.
So, I start here, on the StoreID field, and I want to define a relationship.
I can right-click on the heading and say, Create Relationship, and because I've started on that field, this is already filled in.
We're starting from the demo table, the StoreID column, I choose Related Lookup Table, Store Info, and then, choose the Store field and click create and that's it.
I've now joined these two tables together, which allows me to create pivot tables very easily from both tables.
It also allows me to use the related function over here, to create a calculated column that would rely on data in that other table.
Very easy, easy way to go.
Alright, well, thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ Music ]
PowerPivot for the Data Analyst 5: Defining Relationships.
Well, hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Now, in this episode, I want to talk about manually defining a relationship. Now, it's possible to go ahead and create the pivot table and let Excel try and auto-detect the relationship.
But, if you're a better-safe-than-sorry kind of person, let's just define the relationship; and, boy, is it easy to define this relationship.
So, let's take a look at our data.
Here's my 1.8 million row data set.
You see I have a field called "StoreID".
And, then, here's my 144 row lookup table-- it has a field called "Store".
And there's a one-to-one relationship for each StoreID on the 1.8 million rows-- there'll be a corresponding store here-- and, of course, you know, before PowerPivot, this would be a lot of VLOOKUPs to join this data together.
1.8 million rows of VLOOKUPs for every single field, here, would take forever to calculate.
Watch how easy this is to do.
So, I start here, on the StoreID field, and I want to define a relationship.
I can right-click on the heading and say, Create Relationship, and because I've started on that field, this is already filled in.
We're starting from the demo table, the StoreID column, I choose Related Lookup Table, Store Info, and then, choose the Store field and click create and that's it.
I've now joined these two tables together, which allows me to create pivot tables very easily from both tables.
It also allows me to use the related function over here, to create a calculated column that would rely on data in that other table.
Very easy, easy way to go.
Alright, well, thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ Music ]
