How to Unpivot Data in Excel using Power Query

Data in a crosstab form is good to use as a report because it’s easily understandable. But the problem occurs when you need to use it further.

In this situation, you need to put in some effort and spend your precious time to make it reusable. In the below example, you can see crosstab data. To make it reusable the best way is to UNPIVOT it, yes, just like this one.

And to unpivot data, the best way is to use the POWER QUERY. So today in this post, I’d like to share with you simple steps to convert a crosstab data into a table using POWER QUERY’s unpivot option.

Steps to Unpivot Data in Excel with Power Query

Using Power Query for un-pivoting a data table is pure fun. You will be surprised at how easy it is. But before you use it, you need to install a power query add-in if you are using Excel 2013 or 2010 and in Excel 2016, it’s already there as “Get & Transform”. Make sure to download this sample file to follow along.

• First of all, select your cross-tab data and go to the “Data Tab”.

• In the data tab, go to Get & Transform → From Table.

• When you click on it, it will convert your crosstab data into an Excel table (If already not).
• And, it will instantly it will load data into the power query editor.

• From here we need to select all the columns we want to unpivot.
• For this, select the Jan column and press and hold the shift key and select the Dec column.
• After that, right-click on it and select “Unpivot Columns”.

• Now, all 12 columns are unpivoted into two columns. One is for the month and one is for the amount.

• The last thing you need to do is to rename columns.
• For this, right-click on the column and rename it (for both).

• In the end, click on “Close and Load”.

Now your crosstab data is converted into simple data and you can use it to create pivot tables and all other things for further analysis and sales reporting.

Conclusion

As I said using power query is fun. It makes your unpivoting work so much easier that you don’t have to waste your time and efforts.

Otherwise, if you go with manual methods you don’t know how many times you need to cut-paste your data. Even if you use VBA, you need time to write code.

And, power query is smart enough to understand the structure of your data.

I hope you found this method useful.

Now, tell me one thing. Have you ever tried to unpivot any kind of data? Which method you have used? And, what do you think while comparing your method with the power query? Share your views with me in the comment section, I would love to hear from you.

And please don’t forget to share this tip with your friends.