Bottom line: Learn how to use Power Query to unpivot or normalize your data to get it ready for use in a pivot table.
Skill level: Intermediate
Video: How to Unpivot Your Data with Power Query
In the following video I show a simple example of how to unpivot your data with Power Query in Excel.
Video best viewed in full screen HD.
Download the Excel File
Download the Excel file to follow along.
Why Do I Need to Unpivot My Data?
Often times we receive data that is NOT in the right format for use in a pivot table.
This could be a report that is exported from a system of record, or it could be a spreadsheet that is being manually updated. Either way, getting this data in the correct layout is the most important step in creating a pivot table.
I have other articles that explain why the structure of the source data is so important and how pivot tables work. Both these articles should help you understand why this process is so critical.
Power Query’s Unpivot Button
Fortunately, Power Query has an Unpivot Button that makes it really fast and easy to unpivot in Excel.
It’s basically a 3-step process:
- Load your data into Power Query (create a query)
- Unpivot the selected columns to normalize the data.
- Output the results to a new worksheet in Excel.
The Unpivot Columns button is located on the Transform tab of the Power Query Editor window.
Checkout my article on an Overview of Power Query if you haven't heard of this awesome (free) tool.
A Simple Example of Unpivot
The following screencast shows how to unpivot a sheet that contains inventory data.
The original sheet is laid out in a horizontally with columns for the different products. To use this data as the source of a pivot table, we need to put all the product names in a single column and all the inventory values in another column.
The Unpivoted Table in the image above can be used as the source data of a pivot table. This layout will make it easy to create summarized reports with different layouts.
Additional Resources
- Overview of Power Query – Heard of Power Query but not sure what it is exactly? This article explains what Power Query is all about along with some of my favorite features.
- The Complete Guide to Installing Power Query – Power Query is a “free” add-in from Microsoft for Excel 2010 and 2013. This guide explains which versions of Excel you need, and how to install it.
- How to Structure Your Source Data for a Pivot Table – Learn why your data needs to be unpivoted or normalized before it can be used as the source of a pivot table.
- How Pivot Tables Work – Learn the mechanics of a pivot table and what happens when fields are placed in each area.
Please Leave a Comment
Please leave a comment below with any questions. Thank you!
I was working on a VBA procedure to do that.
But it’s very easy with Power-Query.
Thank you very much Jon
Thanks Hossat! I developed a VBA add-in once that did this as well. But you’re right, Power Query makes it much easier. 🙂
John,
Kudos! This is an excellent and very practical example of how to use Power Query. Thank you very much.
Woody
Thanks Woody! 🙂
Nice tutorial of what is a common problem I am sure. Thanks Jon.
Thanks Bob!
Thanks Jon…2 great tutorials (including the one on using Index to do the same)…much appreciated!!
Thanks RJ!
Hi Jon, I have 2010 professional and I have installed Power Query but I can’t get past step one being when I’m in a pivot table the insert table is greyed out ;(
It not when I’m outside the pivot table.
Any ideas?
Thanks,
Sam.
Hi Sam,
The Power Query Table button only works for importing regular Tables, not Pivot Tables. You can convert the source data of your pivot table to a Table, then import it to Power Query. I hope that helps.
Am interested in Power Query and presently have Office Professional 2010 – NOT Professional Plus 2010, so as I understand it, Power Query cannot be installed. Which do you recommend for an upgrade – 2013 (and download the PQ add-on) or 2016 with it included in Office 365 ProPlus?
Hi Elmarie,
I would first try to download and install Power Query for your version of Office. I have heard that it works for some users on different versions of 2010. If you are going to upgrade then I would go with the latest and greatest, 2016. Microsoft will continue to roll out new features and functions for 2016. They are already releasing updates with new functions and charts. This is something we have never seen in a previous version of Excel, and I think the constant updates will make Excel a lot better going forward. I hope that helps.
Thank you so much! I have known about Power Query before but I don’t expect that it is so helpful. I usually using a model formula to transform data using INDEX, MATCH, MOD, VLOOKUP function but now it is so easy. Great to know.
Thanks Duc! Power Query is a great tool that can replace many manual processes.
Nice tutorial, very clear thank you
Jon – very helpful! Most of our data was already formatted by month across columns. We spent a lot of time converting the data into a list to then use pivot tables for reporting and analysis. We used your index-match method, and also developed VBA code to accomplish the same, but the new unpivot function in Excel Get & Transform will greatly reduce time.
Thanks Bob! I completely agree. I have developed several macros to accomplish this task, but Power Query can easily handle most of relatively simple transformations.
Hi Jon,
When adding a new column for Juice as your video lecture, instead of including Juice into product column, it created a new column for Juice when I hit refresh on other sheet.
Please advise.
Thanks Jon.
When i close and load the query sheet it gives me this msg ” data cannot be retrieved from the database ” and stop loading
Hi Jon,I enjoy your great tips,the problem is that i m using my work PC of which I m restricted to use online therefore unable to watch videos or webinars.Can I save your video files without watching them live?
Awesome tool
Hi Jon, Thank you so much for your lessons! Very informative.
I am running into an issue. I unpivoted the values from the “consultant name” column to obtain two value types in the same column – values associated with the number of records touched by “date” columns and values associated with the “hours billed” column.
Then I typed in a comma to the beginning of the hours billed column heading and used a delimiter to split the Hours billed to a different column . Unfortunately it leaves behind a null that once I pivot the the column it returns an error that “We cannot convert the value null to type text”. If I could figure out how to cure that error I should be able to see the hours billed in one column and the dates in another column. I am wondering if I am not using the split correctly. Or if I should not have two value types in the same column. Any direction is totally appreciated! Many Thanks.
[…] Here is an article and video on exactly How to Unpivot Your Data with Power Query. […]