Excel Unplugged

The New Pivot Column option in Power Query

If some pictures are hard to view, you can get the PDF of the article The Pivot option in Power Query.

With the latest update of Power Query AddInn for Excel quite a few new features were introduced, but none as sweet as the new Pivot option. To get it you must update your Power Query to at least

The Pivot option in Power Query

Just to clarify, the ability is not new, but it just became much more simpler use. Up until now, you could do this, but you had to use the Table.Pivot function which you can see in action in this post on Criss Webb’s BI blog. But now, the new Pivot option brings this closer to the masses 🙂 or to put it differently, makes it easier to use.

Let’s look at an example. This is our data, formatted as an Excel Table of course.

The Pivot option in Power Query

Now we go to Power Query and choose From Table.

The Pivot option in Power Query

Now in the Query Editor we select Transform/Pivot Column

The Pivot option in Power Query

We get the following dialog box, where we select the column and the function by which we wish to aggregate that column.

The Pivot option in Power Query

And this is what we get. A full blown Pivot Table 🙂… Well not quite but with a few tweaks, this can be a really useful data. The tweaks are bonuses that can really improve your data for further use. You can find a few tweaks bellow.

The Pivot option in Power Query

First, just a better view at the Table.Pivot function. As to show, that the principles remain the same, only the way we use them has changed.

The Pivot option in Power Query


A first tweak is to replace the null value with a zero.

The Pivot option in Power Query

The Pivot option in Power Query

And then the famous Unpivot function, where we select all the month columns and select the Unpivot Columns on the Transform tab.

The Pivot option in Power Query

And this is almost as close to eternal happiness as Excel can get you :). You can read more about the Unpivot Columns function seen in action here.

The Pivot option in Power Query

Now after all is said and done and you add data to your initial table, you have a simple refresh button that will repeat all the actions including Pivot Columns and Unpivot columns for you.

The Pivot option in Power Query

Comments 5

  1. Loz says:

    When I “Load To…” from a Power Query query that has Pivot Columns into Excel, only one column from the PQ result appears in Excel – the leftmost column.

    This might be by design (I’m new to PQ with pivot columns).

    But how do I load the pivoted PQ query output into Excel for displaying?

    1. From the written, it must be the overlapping data in Excel or hidden columns. Basically what you see in Power Query as the result of the final step, that is what you get in Excel after loading to table.

  2. Didier Maignan says:

    With this function, only one aggregate value can be displayed at the end, here : Value in your case, but what if I have several value to aggregate : even 2 seems impossible, in order to get several informations on a single line :
    is it possible to get multiple aggregations like a classic pivot table.

    1. Sure, but that wouldn’t be a Pivot Column but it would be a Group By command in Power Query.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.