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
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.
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”.