Power Query: Fun with (Un)PivotTable

Welcome to our Power Query blog. This week, I look at a use for unpivoting.

John, my imaginary salesperson, has been creative with his accounts again…

I asked for a monthly total, but I wanted a single month column rather than a column for each month – that way, it’s much easier to add future data.

I start by extracting the data into Power Query using the ‘From Table’ option on the ‘Get & Transform’ section of the ‘Data’ tab.

I opt to take the defaults as I have headings.

Usually, I don’t have a problem with the automated step that Power Query carries out to set the data types, i.e. ‘Changed Type’.  However, this could cause me problems if John adds (or removes) months from his table as the month columns have been specified:

I delete this step.

On the ‘Transform’ menu, I have several options when it comes to unpivoting columns. I can unpivot them all, or those I select, or those I don’t select. As for the ‘Changed Type’ step, it’s important that I don’t end up with a step where the months columns are named. Although I want to unpivot my month columns, I don’t want to name them explicitly. The answer is to select the Expense Type column, and opt to unpivot the other columns instead.

The M code generated is:

This is good because there is no mention of the month column names.  The months now appear as data under the Attribute column.  I rename this column Month, and I rename the Value column to Amount.  I also set the data types, since I had to delete that step before. 

My data is now ready to append and update if further months are added. I ‘Close & Load’ to load the transformed data into my Excel workbook.

I add a month to the original data to check that everything works as expected:

When I refresh the data, I can see that my transformed data is now complete.

Next time I will look at a more complex example with unpivoting.

Come back next time for more ways to use Power Query!