Define a relationship between two tables in PowerPivot. Podcast #1242 shows you how to link tables on two different sheets to create a Pivot Table.
"Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
"Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel in depth, chapter 25.
PowerPivot Relationships.
Hey! Chapter 25 is all about power pivot.
The great new add in, that came from Microsoft Power Pivot.
Lets us, mashup various data sets.
And so, I have a couple of different tables here sales and weather.
One of those classical mashup, let's see how sales vary by weather.
When we go over to Power pivot?
Say we have our two tables to your sales and weather.
We want to link, these two tables together.
We want to relate them, when I set up a relationship.
Sales has date, weather has date.
Now, all of these sales are for one city.
So, we don't have a secondary key there to worry about.
We could just let powerpivot do this relating force in the pivot table.
But, instead I'm going to explicitly create this.
I want to relate date in this table, to date in the other table.
I come here to the date header of the first table and will right click and say Create Relationship.
And here we say we're going to go from sales, Column date and we want to go to weather and because weather has something called date.
It automatically fills that in for, click [ Create ].
And we now have a link between those two items.
When we create the pivot table now.
You'll see that we have all of the items from our sales table as well as the items from our weather table linked together without any hassles at all.
So one of the great things about Power Pivot, is the ability to take those two sheets that have a common key between them.
Hey! I want to thank you for stopping by.
Will see you next time for another netcast, from MrExcel.
Excel in depth, chapter 25.
PowerPivot Relationships.
Hey! Chapter 25 is all about power pivot.
The great new add in, that came from Microsoft Power Pivot.
Lets us, mashup various data sets.
And so, I have a couple of different tables here sales and weather.
One of those classical mashup, let's see how sales vary by weather.
When we go over to Power pivot?
Say we have our two tables to your sales and weather.
We want to link, these two tables together.
We want to relate them, when I set up a relationship.
Sales has date, weather has date.
Now, all of these sales are for one city.
So, we don't have a secondary key there to worry about.
We could just let powerpivot do this relating force in the pivot table.
But, instead I'm going to explicitly create this.
I want to relate date in this table, to date in the other table.
I come here to the date header of the first table and will right click and say Create Relationship.
And here we say we're going to go from sales, Column date and we want to go to weather and because weather has something called date.
It automatically fills that in for, click [ Create ].
And we now have a link between those two items.
When we create the pivot table now.
You'll see that we have all of the items from our sales table as well as the items from our weather table linked together without any hassles at all.
So one of the great things about Power Pivot, is the ability to take those two sheets that have a common key between them.
Hey! I want to thank you for stopping by.
Will see you next time for another netcast, from MrExcel.
