Unpivot Data With Power Query
Data in a tabular form is good to use as a report format because it’s easily understandable. But the problem occurs when you need to use it for further analysis.
In this kind of situation, you need to put some efforts and spend your precious time to make it re-usable. And to make it, re-usable the best way is to unpivot it.
And for unpivot data, the best way is to use power query. It’s simple, easy and fast.
Just look at the below data table where I have used power query to unpivot multiple columns into a raw data table which I can use further.
So, today, in this post, you will learn simple steps to convert a crosstab data to into table using unpivot option in power query.
Download this raw data file from here to follow along.
Steps to Unpivot Data with Power Query
Using power query for unpivoting a data table is a real fun. You will be surprised that how easy it is.
But before you use it you need to install power query if you are using Excel 2013 or 2010. In Excel 2016, it’s already there as “Get & Transform”.
Don't have time to read all the step right now?
No worries. Let me send you a Free PDF Sheet so you can read it when it’s convenient for you. Just enter your email ID and name.
100% Privacy. I will never spam you!
So let’s get started.
- First of all, select your crosstab data and go to “Data Tab”.
- In 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”.
Now your crosstab data is converted into a 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 a fun. It makes your unpivoting work so easier that you don’t have to waste your time and efforts.
Other wise if you go with manual methods you I 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
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.
