Excel Unplugged

Unpivot with Power Query

081214_0545_Unpivotwith26.png

One might think that analyzing data with a Pivot table is hard, but Unpivot a Pivot table proves to be even harder. But Power Query or better yet a Query editor proves once more that it is a brilliant tool that can handle Unpivot with ease.

Let’s first elaborate on what Unpivot means. From this

Unpivot with Power Query

to this

Unpivot with Power Query

Or in other words transforming a Pivot table back into a tabular and simply analyzable form. And It’s worth mentioning, it’s not limited to Pivot Tables, it can perform the same task on any two-dimensional data you have.

Now that we understand the problem, let’s get to it.

As the title suggests, a Power Query Add-In is required. Now personally, Power Query went from an Excel Add-In to a SWISS ARMY KNIFE excel tool and a MUST HAVE for every data cruncher out there. It’s free and you can get it here.

Now to begin the process, let’s start with the data table that looks like this.

Unpivot with Power Query

First item of business will be to take this data for a first spin in the Power Query Add-In. This is not a necessary step, but it will show you some beauty of the tool. So we select a cell that is part of the Table and go to Power Query/From Table

Unpivot with Power Query

If this is the first time you’re using this tool it’s an absolute pleasure for me to present to you a window that will become your best friend in no time. Given the proper chance of course 🙂 . It’s called Query Editor.

Unpivot with Power Query

Now this is where the magic happens! If we want to do a pivot table summary like above, we will need year, quarter and month data in separate columns. All we need to do is to place ourselves in the Date column and go to the Add Column/Date/Year/Year command

Unpivot with Power Query

And there it is

Unpivot with Power Query

We repeat the process for Quarters and Months

Unpivot with Power QueryUnpivot with Power Query

And end up with

Again, we could have done the same in Excel by Year and Month functions and Quarters formula but this is much cleaner, easier and even more practical. If our initial table had many rows ( >500,000 ) then the amount of formulas written or calculations being performed would somewhat slow down Excel. But this way we get the calculation done and get it done dynamically! Let’s elaborate. Once the steps above are completed, we go to Home/Close&Load/Close&Load To…

Unpivot with Power Query

And chose to load the result of this query into a new excel Table

Unpivot with Power Query

Now this table comes packed with the Refresh feature…

Unpivot with Power Query

And what this refresh does, it looks into the original Table and grabs any new data that may be there and then repeats the steps taken by the query, which were listed in the Applied steps list of the Query Editor. You can Edit this Query at any time and add or remove steps.

Unpivot with Power Query

Creating the Pivot Table

The key with this pivot table is to have only one field in the Columns section and if there are more fields in the Rows section (in our case Year, QTR and Month), you should format the Pivot Table to a Tabular form and have the Item Labels repeated. The end result should resemble this.

Unpivot with Power Query

And finally the Unpivot Query

Now that we created the Pivot table with the calculations we want, let’s “Unpivot it”. We go to a new Excel Workbook and from Power Query select From File/From Excel

Unpivot with Power Query

You then select a file and the Navigator lets you select the Sheet or Table you want to import. It even gives you a preview of the data.

Unpivot with Power Query

Once the right Sheet is selected, you either select Load or the almighty Edit. If you want to Unpivot it has to be Edit, to get to the Query Editor. This will help us resolve all issues that would occur at a normal Pivot Table import.

  1. The column headings are generic (Column1,Column2,…).
  2. First row of data is obsolete.
  3. The second row of data should be the header row.
  4. Multiple “null” values where zeroes should be.
  5. The Pivot shape.

Unpivot with Power Query

Now we will tackle these problems one by one.

  1. Remove Top Row

Unpivot with Power Query

And then you must specify how many top rows you wish to remove. We will settle for just the one

Unpivot with Power Query

And we get

Unpivot with Power Query

  1. Use First Row As Headers

Unpivot with Power Query

And we get

Unpivot with Power Query

  1. Change “Null” into 0

Select all the columns where you wish to change a value and select Transform/Replace Values

Unpivot with Power Query

And you get

Unpivot with Power Query

  1. Unpivot

Now we select all the columns with the City names… So from Abidjan…to the last column and select Transform/Unpivot Columns

Unpivot with Power Query

And here it is

Unpivot with Power Query

All the Pivot Table data in five columns 🙂 . The steps listed on the right can be individually edited or deleted. But after you select Home/Close&Load, you get a Table in Excel where you just right click and select Refresh and Excel or better yet Power Query will repeat all the steps for you and give you the latest data. I am confident, you can find many uses for this and I hope that it saves you as much time as it does for me.

Comments 12

  1. Olivier says:

    Thanks for that post that seems really interesting.
    However, it is really difficult to read pictures (to smalls), even Zooming In.
    Do you have another format for your post.
    Thanks a lot

    1. Hi,

      glad you liked it. You can get the PDF version here, it should be easier to read if the screenshots are not clear on the page.

      https://excelunplugged.files.wordpress.com/2014/08/unpivot-with-power-query.pdf

  2. Steve Chase says:

    Great post! Thanks for sharing. Love the screen shot examples and easy to follow illustrations.

  3. Jacob Halstead says:

    Brilliant post and super useful information! Thanks for your hard work on this.

  4. thanks for introducing PowerQuery – will be fun to explore this tool, and definitely useful for myself and for some of my customers. Glad you introduced me into ist Advantages by this deliberate post!

    1. You’re welcome. I think the whole PowerBI solution from Microsoft is a great step up for each and every Excel user that deals with importing data and visualization of that data. I’m glad you recognized it for the brilliant tool which it is and hope it helps you on your quest for eternal happiness.

  5. Javier says:

    Hi, thanks for the post. Any chance I can do it with 2 values for the same atribute?

    Cheers,

  6. Dane says:

    Hello – how do you account for new columns of data being added when using the 1.Change “Null” into 0 step?. I’ve found that replace values does not pick up new columns when added to the table, thus the unpivot removes rows with null values. How can I make the replace values dynamic as new columns are added.

    Thanks,
    Dane

    1. Great question Dane,
      the obvious solution would be to use the Pivot Table’s native “For blank Cells Show” setting in the Pivot Table Options window in Excel. But there must be a way to do this in Power Query and I will look into it and get back to you…

      1. Dane says:

        Thank you for looking into this Gasper. I imagine there’s two possible ways:
        1) Make replace values dynamic to capture new columns as they are added
        or
        2) Keep the nulls through the unpivot process and then replace them when the values are in a single column.

        I’ve not been able to fine a way to do either.

        Thanks again.
        Dane

        1. Here’s the simplest of examples. Col becomes the list of all column names (dynamic)… ColList is then all but the first column and then that is provided into the last step that replaces your values…
          let
          Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
          Col = Table.ColumnNames(Source),
          ColList=List.Skip(Col, 1),
          #”Replaced Value” = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,ColList)
          in
          #”Replaced Value”

          1. Dane says:

            Hi Gasper,

            Your solution worked perfectly!!!! Thank you very much.

            Dane

Leave a Reply

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

%d bloggers like this: