Un-Pivoting Data in Power Query

I was fooling around with the latest build of Power Query (for Excel 2010/2013), and I’ve got to say that I’m actually really impressed with this piece. I think the user experience still needs a bit of work, but once you know how, this is really simple.

Whenever I teach courses on PivotTables people want to eat them up, but often they’ve already got their data in a format that resembles the PivotTable output, rather than a format that is consumable by the Pivot engine. Take the following set of data.

SNAGHTML111988

A classic setup for how people think and want to see the data. But now what if the user wanted to see the records by month under the category breakdowns? An easy task for a Pivot, but it would require a bit of manipulation without it. So how do we get it back to a Pivot compliant format?

With Power Query it’s actually super simple:

  • Step 1: Select the data and turn it into a table
  • Step 2: Select Power Query –> From Table to suck it in to Power Query
  • Step 3: Un-pivot it

Okay, so this one takes a bit of know how right now. If you just click Unpivot, you get something really weird:

image 

What actually needs to happen, which isn’t totally intuitive, is we need to click the header for the January column, hold down CTRL and click the February and March column headers:

SNAGHTML18e3d9

Once we’ve done that we click Unpivot and TA-DA!

image

So by virtue of being able to choose one or more columns, it’s actually quite flexible as you can choose HOW to un-pivot, which is uber-awesome. It’s a shame the UI doesn’t help you realize that today, but hopefully that gets fixed in future.

At any rate, you can right click the headers to rename the columns, then click “Apply and Close” and we end up with the un-pivoted source in our worksheet. And now we can create a new PivotTable off that source:

SNAGHTML1b48f1

Even better, if we add some new data to our original report:

SNAGHTML1cfc48

We can then refresh the query on the worksheet and the records are pulled in. With a refresh on the Pivot as well (yeah, unfortunately you can’t just refresh the pivot to drive the query to update) it’s all pulled in:

SNAGHTML1dae1a

Pretty damn cool!

12 thoughts on “Un-Pivoting Data in Power Query

  1. Pingback: Excel Roundup 20131118 | Contextures Blog

  2. Hi Ken,
    Using a pivot table you can do this too.
    1. Add the pivot table wizard to your QAT or prees alt+d,p
    2. Select Mutliple consolidation ranges
    3. Click next and leave the default selected and click next again.
    4. Select your table and click Add
    5. Click Finish
    6. Double-click the Grand total cell bottom-right of the pivot table.

  3. That's true, Jan Karel. But once you started needing the super secret Excel MVP handshake or decoder ring to find the feature it became pretty inaccessible to most people. (Not that it was really discoverable being hidden where it was in that setup either.) This brings the feature up front and centre, which is much better, IMO. 🙂

  4. Pingback: Daily Dose of Excel » Blog Archive » UnPivot via SQL

  5. Good stuff, Ken. I haven't played around with this much, because I don't have it at work. So hard to justify the time when there's plenty of stuff in good-ol VBA that I can learn on the Boss' dime 🙂

    Hey, just did a related post over at DDOE on how to do an UnPivot using SQL. Check it out.

  6. Pingback: Pivoting Data In Power Query | Chris Webb's BI Blog

  7. Great article, but I need to take the unpivot one step further, is it possible to unpivot sub catagories, So for your example, if 1/31, 2/28, and 3/31 had sub categories of x,y, and z. How would you unpivot?

  8. Pingback: Unpivot Nested Headings With Power Query | Bob's BI Ramblings

  9. Pingback: Transform already-pivoted tables to PivotTable (The Definitive Guide to Unpivot with Power Query in Excel) - Get & Transform Data in Excel 2016 - Site Home - TechNet Blogs

  10. Pingback: Transform already-pivoted tables to PivotTable (The Definitive Guide to Unpivot with Power Query in Excel) | Data Chant

Leave a Reply

Your email address will not be published. Required fields are marked *