Power of UnPivot in Power Query

Unpivot is a process in data transforming. This is present inside the Power Query Editor window. Using this feature, we can transform the data to the required structure when they are not in the proper format.

 Let us take a scenario to understand this feature better, imagine you have 3 years of sales data (Monthly Product Sales) where the data has been managed in such a way month names are kept as headers and product names are row wise. Check out the screenshot below.

Problem in this arrangement I cannot do even simple filtering for a specific quarter or year, because horizontal filtering is not possible in excel.

Another problem is when I analyze this data to compare year wise or quarter wise sales for the product, this data structure will not help.

So I need to transpose this data in order to use it for my requirement. Paste special transpose will not solve my requirement. Hence using the Power Query to resolve this issue

Click here to download the excel

Steps involved in UnPivot Data Using Excel Power Query

Select your data and go to “Data” tab and click on the option ‘From Table/Range’ which is a part of ‘Get & Transform Data’ group

A dialog box gets opened with the range of cells as below: Check whether the data has been completely and “My Data Has Headers” option should also be active.

Click OK.

This opens the “Power Query Editor” window and you can see your data has been loaded in.

Now start selecting entire month columns in the loaded table.

You then need to go to the “Transform” tab and select “Unpivot Columns”.

Now the magic happens, the data transforms beautifully and becomes very report friendly

In order to use this transformed data, you need to load this table back to excel.

Go to the “File” tab and choose “Close & Load”.

Process completed

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.