Episode #1229 shows you how to manually define a relationship between two tables in PowerPivot.
...This episode is the video podcast companion to the book, "PowerPivot For The Data Analyst: Microsoft Excel 2010", from Bill Jelen a.k.a. MrExcel.
...This episode is the video podcast companion to the book, "PowerPivot For The Data Analyst: Microsoft Excel 2010", from Bill Jelen a.k.a. MrExcel.
Transcript of the video:
MrExcel podcast 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 wanna 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 Road data set.
See I have a field called Store ID and then here's my 144 row look up table.
It has a field called store, and there's a one-to-one relationship for each store ID 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 Store ID 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 Store ID 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.
Well, thank you for stopping by.
See you next time for another netcast from MrExcel.
Well, hey welcome back to the MrExcel netcast.
I'm Bill Jelen. Now in this episode I wanna 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 Road data set.
See I have a field called Store ID and then here's my 144 row look up table.
It has a field called store, and there's a one-to-one relationship for each store ID 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 Store ID 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 Store ID 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.
Well, thank you for stopping by.
See you next time for another netcast from MrExcel.
