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

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.

Now we go to Power Query and choose From Table.

Now in the Query Editor we select Transform/Pivot Column

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

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.

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 TWEAKS

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

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

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.

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.