How to Unpivot Data in Excel using Power Query

Steps to Unpivot Data in Excel with Power Query

• 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 power query editor.

• From here we need to select all the columns we want to unpivot.

• For this, select Jan column and press and hold the shift key and select Dec column.

• After that, right-click on it and select “Unpivot Columns”.

• Now, all the 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”.

Conclusion

As I said using power query is fun. It makes your unpivoting work so 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 time 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 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.