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.
Unpivot Inventory Sheet With Power Query.xlsx (21.3 KB)
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!
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.
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.
Nice tutorial, very clear thank you
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.
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.
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.
Thanks Jon…2 great tutorials (including the one on using Index to do the same)…much appreciated!!
Thanks RJ!
Nice tutorial of what is a common problem I am sure. Thanks Jon.
Thanks Bob!
John,
Kudos! This is an excellent and very practical example of how to use Power Query. Thank you very much.
Woody
Thanks Woody! 🙂
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. 🙂