The step-by-step video below shows how to unpivot data in Excel, using Power Query. This creates better source data that you can use to build flexible pivot tables. And this technique doesn’t change your original data!
Why Unpivot Excel Data?
Before you can build a flexible pivot table in Excel, you might need to rearrange your source data.
For example, in the screen shot below, the sales amounts are entered horizontally. There is a separate column for each month’s sales amounts.
It’s easy to enter data in this layout, but the monthly columns don’t work well for pivot tables.
Vertical Layout for Pivot Tables
If you have data that’s in a similar horizontal layout, you can “unpivot” the data, to get all the amounts arranged vertically, in a single column.
The screen shot below shows an example of rearranged data. Now,
- all the sales amounts are in a single column
- all the month names are in a single column
- each row has details for a single product sale
Video: Unpivot Excel Data with Power Query
Follow the steps in the video below, to fix your data, if it’s in a horizontal layout. There are written steps and a sample file on the Unpivot With Power Query page on my Contextures site.
Tip: For other ways to unpivot Excel data, without Power Query, go to the Fix Pivot Table Source Data page on my Contextures site.
Video Timeline
- 00:00 Introduction
- 00:27 Named Excel Table
- 01:24 Start Power Query
- 02:02 Rename Query
- 02:19 Delete Step
- 02:42 Remove Column
- 02:57 Unpivot Data
- 03:31 Rename Columns
- 03:50 Detect Data Type
- 05:10 Load Data
- 05:41 Refresh Data
- 05:59 Get the Sample File
Null Data
When you use the Unpivot command in Power Query, it automatically removes the null data.
If you want to keep the nulls, there are steps in the video below, by Ken Puls.
Get the Sample File
To test the Power Query Unpivot technique, you can go to the Unpivot With Power Query page on my Contextures site, and download the Excel sample workbook.
The file is in xlsx format, and is zipped. There are no macros or queries in the file.
_______________________
How to Unpivot Excel Data with Power Query
_______________________