Data Bear’s Monday Motivation Blog Post

Happy Monday all.  Today I’ll be showing you the unpivot function in Power Query.  This is a very helpful modeling tool that helps you shape your data in a more user-friendly way. It transforms (no pun intended) your visual representation options.

We all know that data is messy and sometimes requires a bit of work to get it in a way to use it meaningfully.  One of these hurdles comes in the form of categories (or dates) being represented in columns rather than rows.  This prevents filtering options and other issues that require many calculated columns to get what you want. One way to address this by using Unpivot in Power Query.

See below an example of this, where the categories are in columns.

If you leave the data as is, you are stuck with few options of visual representation and even fewer filtering options, apart from a date filter.

When seeing the benefits of Unpivot in Power Query data in this format you open up the possibilities and interactive reporting 10 fold.

How to unpivot in Power Query

In power query, you have 2 options, either Unpivot Other column or Unpivot columns.  You can use either one.  In this instance we only have 1 column that needs to remain unchanged, therefore it is easier to use the Unpivot other columns option.  I do this by highlighting the date column (click on column), right-clicking, and choosing Unpivot other columns.

After this action, the data looks like this.

Remember to rename your columns as the default ones might confuse you.

Default column names

Rename by double-clicking on the column header.

Now you have more options and your report is more interaction without having to add other calculated columns.

Even though the original bar chart was something you were interested in to compare sales across products, you can still do that. With the added option of viewing each product by itself.  This filtering option was not possible before.

The table format from before can also be achieved by using a matrix visual and putting the product in the column.

This is a very simplistic view of what you can achieve using unpivot but it one of the most helpful tools I’ve come across.  See here is another great way of using it when working with budget data.

Visit our page for more great posts.