Unpivot data quickly with Power Query [tutorial]

Posted on September 29th, 2015 in Pivot Tables & Charts , Power Query - 9 comments

Power Query (Get & Transform data in Excel 2016) is a must have tool, if you wrangle with data every day. Here is a quick introduction, in case you are new.

Let’s learn how to use Power Query to unpivot data.

Essentially, we are trying to go from left to right in this picture.

unpivot-data-using-power-query

Doing something like this thru either formulas or VBA can be very complex. But Power Query can get you unpivoted data in just a few clicks. Sounds interesting? Read on.

Tutorial: Unpivot data using Power Query

Step 1: Set up your pivoted data as a table

If you want Power Query to work with data in Excel, it must be in table form. So select any cell in the pivoted data and press CTRL+T to turn it in to a table.

At this stage, we get this:

set-up-pivot-data-as-table

Step 2: Load table data in to Power Query

While keeping the selection inside pivot data, go to Power Query ribbon (or Get & Transform area of Excel 2016 data ribbon) and click on “from Table” button.

add-table-data-to-power-query

This will take your table data and load it in to a new query in Power Query. It looks like this:

initial-state-of-our-data-grand-totals-to-be-removed

Step 3: Get rid of grand totals

When unpivoting data, we don’t need the grand totals. To remove them,

  1. Select the grand total column
  2. Click on “Remove Columns” button in query editor (Power Query window)
  3. Click on “Remove Rows” button, select remove bottom rows option.
  4. Enter the number of rows as 1

At this stage, grand total column & row are gone. We end up with this:

data-after-grand-totals-gone

 

Step 4: Fill down the missing region names

If your pivot table has null / blank values in the first column, you can fill them with values from above cells using the Fill option of query editor. Select the Region column and click on the Fill button from transform ribbon. See this demo:

fill-down-power-query-demo

Step 5: Remove sub-total rows by filtering them away

Click on the filter button next to region and filter away all the sub-total columns too. We don’t need them for unpivoting.

filterin-away-sub-totals

Step 6: Unpivot the data

Now that our data is in correct shape, let’s unpivot.

Select the last 3 columns and click on Unpivot columns button in Transform ribbon.

unpivot-data

And we get the unpivoted data.

final-unpivot-data

You can load this data to Excel or to your data model for further processing.

Download example Power Query workbook

Please click here to download the example workbook for this tutorial. To examine the query settings and power query steps,

  1. Open the workbook
  2. Go to Power Query ribbon (or Data ribbon in Excel 2016) and click on Workbook Queries Show Pane option.
  3. Right click on “Unpivot Data” query and choose edit
  4. This opens the query editor. You can examine the steps in the query steps pane to right.

Learn more about Power Query / Get & Transform data:

Power Query (or less intimidating Get & Transform data in Excel 2016) is an impressive technology to help you deal with common data problems easily. If you are an analyst who relies on Excel, learning Power Query is going to make you super productive. Check out below tutorials to get started with this amazing feature.

How do you unpivot your data?

I used to write VBA programs to unpivot my data. But now that I have Power Query, I use it anytime I need unpivoting.

What about you? How do you unpivot your data? Please share your thoughts and tips in the comments section.

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

9 Responses to “Unpivot data quickly with Power Query [tutorial]”

  1. milang says:

    In Step 3 you could filter out Salesman column, and remove nulls, excluding totals and subtotals in that way.

  2. Maxim says:

    Thanks, very helpful!

  3. Mark W says:

    Ummm...what am I missing. 2 easier ways.

    Why not just double click the grand total. The detail then jumps into a separate sheet of everything in the original spreadsheet.

    A second way is to just place everything that you want into the same rows and columns. Go to Design-Report Layout-Show in Tabular Form and Design-Report Layout-Repeat All Item Labels. Then copy everything to a new spreadsheet, Paste Values and then convert to a table.

    This all seems way easier.

    • Chandoo says:

      Hi Mark,

      Of course, if you could double click (or change pivot layout), you then also have access to original data. So no need to go thru this process. The Power Query technique is useful when you just have data in pivot format (for example a report dump) and want to reshape it for some other process.

      • Arvind Jain says:

        I agree with Chandoo. I too wondered whole night why somebody would unpivot when the basis of making Pivot is itself based on the table already existing. This illustrates how powerful power query is.

        Thanks once again Chandoo!

    • Danny says:

      Yes true Mark,
      was my first thought too -

      but during my courses I get many people who get data from a datawarehouse and they don't have access to the source data - their question typically is how to analyse the data with sorts/filters and their own pivot tables... so in these scenarios these power query tools are a blessing.

      tikè?

  4. Rudra says:

    Hi
    Can I know how to remove subtotals in querry window? I have 92 rows with 'Sub Total' and couldn't do it with power querry. Please help.

    Regards
    Rudra

    • Maxim says:

      Hello Rudra. May be you can filter them out? Just make text filter on your column with condition like "Not Contains 'Sub Total'
      Here is how it can looks in M language:
      = Table.SelectRows(Source, each not Text.Contains([Region], " Total"))

  5. Artieboy says:

    Hi Chandoo

    I have a question about the pivot columns themselves.
    Here you have FastCar, RapidZoo, SuperGlue, all in nice alphabetical order. But what if they were SuperGlue, FastCar, RapidZoo and you wanted them in alphabetical order? I guess you could just move the columns.

    But what if you bring in, for example based on your screen shot, different countries which now introduces other columns such as MendozaWine, NiceCheese, OpenHat, and SlowMetro. Now how will the column order look?

    What can you do to always ensure that the columns come out in the desired order after pivoting, regardless of what columns they are: FastCar, MendozaWine, NiceCheese, OpenHat, RapidZoo, SlowMetro, SuperGlue.

    Any help will be appreciated. Thanks and I hope I didn't confuse anyone.

Leave a Reply