The MrExcel Podcast returns with a demo of how to mash up two data sets in PowerPivot. Episode #1178 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, it's been 33 days.
I took 33 days off.
I finished five books.
Here's the book that I finished, yesterday.
PowerPivot for the Data Analyst, like I gotta tell you Power Pivot is a great feature coming in Excel 2010.
And if you happen to be watching this on Friday or Saturday, I want you to go out and vote for my entry and the great PowerPivot contest.
It's that, Jewelry Sales Mashup.
Go to, "alpha geek challenge", and you can vote up until Saturday.
So, I appreciate your vote there.
I want to talk a little bit about PowerPivot.
We've done a couple of podcasts before I want to show you how we can take power pivot and mash data up.
So, here I have sales data.
You see that it has a customer information and here I have customer and sector.
I want to put those two tables together.
So, what do we do?
Well, normally that would be VLOOKUP right.
Not gonna have to do that.
Watch, how easy this is going to be with power pivot.
I'm going to create a linked table, and there's our first table in power pivot.
I come back to the sectors, create a linked table again, and so now in power pivot I have two different tabs.
The sales tab and the sectors tab.
Go to the sales tab, the customer, right click, create relationship.
Relationship between sales and sectors is it smart enough customer to customer.
Click [ create ].
Alright! Now, let's create a pivot table.
Single pivot table, look at this will create even four pivot charts, all controlled by the same slicer.
Click [ OK ].
Alright! Check out this brand new field list over here.
So, we have all of the fields from, the one table and all of the fields from the other table.
If I want to see sales by sector, I click [ sector ], I click [ revenue ] and BAM!
It joined that data up isn't that the coolest thing.
Also look here, we have drop zones for slicers, vertical slicers, horizontal.
Let's put year, in slicers horizontal.
Maybe month, maybe region.
And now, I can update this analysis very quickly.
There's 2009, or there's 2009 and 2010.
Just for the East region, very cool, very amazing.
Great set of tools that are coming in Excel 2010.
If you buy Excel 2010 PowerPivot is absolutely free.
So, between now and the release, we'll be taking a look at some more power pivot tools.
Also help to bring the dueling Excel podcast back.
I'll be out in Seattle, next week.
Mike Girvin, will do some dueling podcast live.
So, I want to thank everyone for your patience.
While, I finish those books.
See you next time for another netcast from MrExcel.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, it's been 33 days.
I took 33 days off.
I finished five books.
Here's the book that I finished, yesterday.
PowerPivot for the Data Analyst, like I gotta tell you Power Pivot is a great feature coming in Excel 2010.
And if you happen to be watching this on Friday or Saturday, I want you to go out and vote for my entry and the great PowerPivot contest.
It's that, Jewelry Sales Mashup.
Go to, "alpha geek challenge", and you can vote up until Saturday.
So, I appreciate your vote there.
I want to talk a little bit about PowerPivot.
We've done a couple of podcasts before I want to show you how we can take power pivot and mash data up.
So, here I have sales data.
You see that it has a customer information and here I have customer and sector.
I want to put those two tables together.
So, what do we do?
Well, normally that would be VLOOKUP right.
Not gonna have to do that.
Watch, how easy this is going to be with power pivot.
I'm going to create a linked table, and there's our first table in power pivot.
I come back to the sectors, create a linked table again, and so now in power pivot I have two different tabs.
The sales tab and the sectors tab.
Go to the sales tab, the customer, right click, create relationship.
Relationship between sales and sectors is it smart enough customer to customer.
Click [ create ].
Alright! Now, let's create a pivot table.
Single pivot table, look at this will create even four pivot charts, all controlled by the same slicer.
Click [ OK ].
Alright! Check out this brand new field list over here.
So, we have all of the fields from, the one table and all of the fields from the other table.
If I want to see sales by sector, I click [ sector ], I click [ revenue ] and BAM!
It joined that data up isn't that the coolest thing.
Also look here, we have drop zones for slicers, vertical slicers, horizontal.
Let's put year, in slicers horizontal.
Maybe month, maybe region.
And now, I can update this analysis very quickly.
There's 2009, or there's 2009 and 2010.
Just for the East region, very cool, very amazing.
Great set of tools that are coming in Excel 2010.
If you buy Excel 2010 PowerPivot is absolutely free.
So, between now and the release, we'll be taking a look at some more power pivot tools.
Also help to bring the dueling Excel podcast back.
I'll be out in Seattle, next week.
Mike Girvin, will do some dueling podcast live.
So, I want to thank everyone for your patience.
While, I finish those books.
See you next time for another netcast from MrExcel.
