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