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