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.

Unpivot Power Query

 

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.

Table before unpivot

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.

Bar chart before unpivot Table format

 

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.

Unpivot other columns

After this action, the data looks like this.

After Unpivot

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

Default column names

Default column names after unpivot

Rename by double-clicking on the column header.

New table headers after unpivot

 

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.

Bar chart after unpivot GIF

 

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

Matrix format

 

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.