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

THE TWEAKS

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