Pivot tables are great when the source data is formatting correctly. But today, our data set has the Quarter field stretching across the columns instead of down the rows. This creates an awkward pivot table. The old solutions (see episodes 1787 and 705) took a lot of manual effort. Today, I show how the Power Query tool for Excel 2010 and Excel 2013 make this incredibly easy.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1909.
UnPivot With PowerQuery.
Here's a classic problem.
This has been covered on the podcast a couple of times before, but the answer at this time is so much easier.
I want to create a Pivot Table from this data, but the problem is, that we have one field - quarter - that is a repeating group going across the columns, instead of down.
What we really need is: we need a data set, that has four times as many records and an extra column, that identifies the quarter.
When you try to create a Pivot Table from this data, it just is going to be ugly.
INSERT, PivotTable, OK, let's put Markets down the left-hand side.
I want to choose Q1, Q2, Q3, Q4, and now I want to total those.
Well, I have to go create a calculated item or calculated field to do that.
It's just so unfriendly to deal with this data.
And in the past, we've used this horrific trick from Mike Alexander with the Multiple Consolidation Range, Pivot Table and double click in the grand total.
Grand total… life is so much easier now, that we have Power Query.
Now, at the end of this video I'm to talk about Power Query.
But let me, let's assume, you know what Power Query is.
We come here to the POWER QUERY tab, my data is already in Excel (this is great for pulling data in from other places, but I don't have to do that, it's already here, it's in an existing table).
So we choose From Table, (check) My table has headers, click OK, and we go to the Power Query – Query Editor.
And in the Query Editor you see that we have tabs across the top, for Home, Transform, Add column and View.
So I'm going to choose the first column that needs to be unpivoted and then Shift+click on the last column, that selects those four columns out here, and Transform.
Watch how easy this is: just choose Unpivot Columns and bam, they've now taken that data, it's four times as many records as I had before.
They gave me a heading here called Attribute, so let's choose that column and then right-click and say Rename… and call it Qtr for quarter, and press Enter.
All right, that's good, that's everything we need.
Click Home, say Apply&Close and we go back to Excel, where the original data is still there.
So here's the data worksheet that I started with and here is the result.
From here, we're good to go.
Just do: INSERT, PivotTable, OK.
And now, put Markets down the left hand side, quarters (Qtr) across the top and then Value in that Values area, and we are good to go.
Power Query.
Let's talk about Power Query.
This is part of the new “Power BI” tools, so: Power Pivot, Power View, Power Map, Power Query, and then the Q&A is this whole new suite of Power BI.
And the most of that stuff is for Excel 2013.
However, this piece, which is an amazing and varied set of data cleansing tools, is free, if you have Excel 2010 or Excel 2013.
Just go out to the web search for a “Download Power Query” and you can download it.
Again, if you have 2010 or 2013.
I could spend an entire month doing videos showing all the amazing things, that we can do with Power Query.
But for this one particular problem, where we have to unpivot the data, it is just remarkably easy to go to the Transform tab and use the Unpivot to solve this problem.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1909.
UnPivot With PowerQuery.
Here's a classic problem.
This has been covered on the podcast a couple of times before, but the answer at this time is so much easier.
I want to create a Pivot Table from this data, but the problem is, that we have one field - quarter - that is a repeating group going across the columns, instead of down.
What we really need is: we need a data set, that has four times as many records and an extra column, that identifies the quarter.
When you try to create a Pivot Table from this data, it just is going to be ugly.
INSERT, PivotTable, OK, let's put Markets down the left-hand side.
I want to choose Q1, Q2, Q3, Q4, and now I want to total those.
Well, I have to go create a calculated item or calculated field to do that.
It's just so unfriendly to deal with this data.
And in the past, we've used this horrific trick from Mike Alexander with the Multiple Consolidation Range, Pivot Table and double click in the grand total.
Grand total… life is so much easier now, that we have Power Query.
Now, at the end of this video I'm to talk about Power Query.
But let me, let's assume, you know what Power Query is.
We come here to the POWER QUERY tab, my data is already in Excel (this is great for pulling data in from other places, but I don't have to do that, it's already here, it's in an existing table).
So we choose From Table, (check) My table has headers, click OK, and we go to the Power Query – Query Editor.
And in the Query Editor you see that we have tabs across the top, for Home, Transform, Add column and View.
So I'm going to choose the first column that needs to be unpivoted and then Shift+click on the last column, that selects those four columns out here, and Transform.
Watch how easy this is: just choose Unpivot Columns and bam, they've now taken that data, it's four times as many records as I had before.
They gave me a heading here called Attribute, so let's choose that column and then right-click and say Rename… and call it Qtr for quarter, and press Enter.
All right, that's good, that's everything we need.
Click Home, say Apply&Close and we go back to Excel, where the original data is still there.
So here's the data worksheet that I started with and here is the result.
From here, we're good to go.
Just do: INSERT, PivotTable, OK.
And now, put Markets down the left hand side, quarters (Qtr) across the top and then Value in that Values area, and we are good to go.
Power Query.
Let's talk about Power Query.
This is part of the new “Power BI” tools, so: Power Pivot, Power View, Power Map, Power Query, and then the Q&A is this whole new suite of Power BI.
And the most of that stuff is for Excel 2013.
However, this piece, which is an amazing and varied set of data cleansing tools, is free, if you have Excel 2010 or Excel 2013.
Just go out to the web search for a “Download Power Query” and you can download it.
Again, if you have 2010 or 2013.
I could spend an entire month doing videos showing all the amazing things, that we can do with Power Query.
But for this one particular problem, where we have to unpivot the data, it is just remarkably easy to go to the Transform tab and use the Unpivot to solve this problem.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
