Home > Pivot > Create > Unpivot Unpivot Data with Power QueryUnpivot Excel data, if needed, to change from horizontal report layout to normalized column layout. This step-by-step guide shows how to unpivot using Power Query. This technique creates better source data for building flexible pivot tables. For other ways to unpivot Excel data, without Power Query, go to the Fix Pivot Table Source Data page Author: Debra Dalgleish and Alex Blakenburg |
Video: Unpivot Excel Data with Power QueryUnpivot Excel data, if needed, to change it from horizontal report layout to normalized column layout, which creates better source data for building flexible pivot tables. This step-by-step video shows how to unpivot using Power Query, and there are written steps and a sample file below. Video Timeline
|
Introduction: Why Unpivot 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, there is a separate column for each month’s sales amounts. That layout makes it easy to enter data, but it causes problems if you need to create pivot tables. In the screen shot below, you can see a pivot table built from that data. Instead of one pivot field for amounts, there is a separate pivot field for each month's amounts. To get an annual total, you’d have to :
Those steps would take time, and it would be cumbersome to work with all those fields! |
Normalize Data with UnpivotTo avoid that problem, you can "unpivot" the data, to get all the amounts in one column. The screen shot below shows an example of rearranged data, with all the sales amounts in a single column. In the revised data layout:
After the "unpivot", you 'll be able to build a flexible pivot table from this data layout, with all the monthly amounts in a single field. |
Benefits of Power Query UnpivotYou can use Power Query to quickly unpivot the data, and put it in a better layout for pivot tables. The steps to do that are in the sections below. The good news, if you unpivot your data with Power Query, is:
|
Create Named Tableif your data is not a named table, follow the steps in this video, to convert it to a named table. Doing this before starting the Unpivot, allows you to give the table a meaningful name of your choice. To see the easy steps in creating an Excel Table, you can watch this short video. There are written steps on the Create an Excel Table page. |
Sample Sales Data to UnpivotIn this examples, there is a named Excel table, with sales data. There are 4 columns for product names -- Binders, Clipboards, Pencils, Pens. This table is named tblSalesDE In the steps shown in the following sections, we will unpivot that data, to put:
For example, in the screen shot below, you can see the items from the first 3 rows, after an unpivot.
|
Start Unpivot StepsWARNING: If your Excel data is NOT in a named table, the Unpivot steps below will convert it to a named table.
To start the Unpivot process with Power Query, follow these steps:
|
Power Query Editor StepsThe Power Query Editor opens automatically, with your data showing in the main window. At the left, the source table's name appears - tblSalesDE At the right, there is a Query Setting pane, that shows:
In the centre, there is a grid with the data from the source table. 1) Change Query NameFirst, you should change the query name, instead of leaving the default name. The query name will be used for the output table, so it's good practice to create a meaningful name, and a name that is different from the source data table.
In the screen shot below, I changed the name to UnpivotSalesData |
2) Delete Query StepNext, in the Query Settings Applied Steps list, we'll delete one of the steps - Changed Type
We deleted this step because it hard codes the column heading names, and we want those to be dynamic, in case the product name headings are changed later. In the screen shot below, you can see the hard-coded name of each column, and the data type it was assigned. After the Changed Type step is removed, you can see a simpler formula in the formula box, that refers to the source data table, as shown in the screen shot formula below. |
3) Filter Region ColumnNext, in data table, follow the steps below, to filter the Region column, to remove the total row:
We don't want the Total amounts in the Unpivot results. Note: A new step appears in the Applied Steps list - Filtered Rows 4) Remove Total ColumnThere is a Total column in the source data, and we don't need that in the revised data. Follow these steps to delete the Total column in Power Query:
The Total column is removed, and a new step appears in the list - Removed Columns |
5) Unpivot the DataNext, follow these steps to unpivot the data
The data is unpivoted, and a new step appears in the list - Unpivoted Other Columns Note: Power Query automatically removes the Null records. To keep them, see the steps in Ken Puls' video on YouTube. 6) Rename ColumnsNext, in the revised data table, follow the steps below, to rename the two new columns:
The renamed columns are shown in the screen shot below. A new step appears in the list - Renamed Columns |
7) Detect Data TypeNext, in the revised data table, follow the steps below, to detect the data type for all of the columns:
Power Query automatically assigns a data type to each selected column. A new step appears in the list - Changed Type 8) Check Data Type ResultsAfter using the Detect Data Type command, it's good practice to check each column, before going on to another step. Make sure the data type is correct, and has the right level of accuracy. For example, if the Sales column data type was set as Whole Numbers:
To review the revised data table, follow the steps below, to check the data types:
|
Change Data TypeIn this example, Power Query assigned a Date/Time format in the Date column. We don't need the time, just the date. So, follow these steps to change that data type:
Confirm Data Type ChangeNext, a confirmation message appears, with the title, Change of Column Type, and the following message text:
There are three buttons at the bottom of the message: 1) Replace Current, 2) Add new step, 3) Cancel We want to replace the data type, without creating a new step:
Because we replaced the current conversion, NO new step appears in the list |
9) Load Revised DataThe final step is to load the revised data to Excel, so follow these steps:
The Import Data dialog box opens, with import settings that you can select.
|
Table With Unpivot DataOn the new worksheet, the data from the Unpivot Data query is in a named Excel table. You can change its appearance by applying a different Table Style. Note: Unlike new tables that you create with the keyboard shortcut, Ctrl+T, the table loaded from Power Query does not use the workbook's default Table Style Refresh Unpivot DataIf you make changes in the original data, refresh this Unpivot data, to see the updated results.
|
Null Data in UnpivotWhen 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. |
Unpivot Source Data - Manual StepsIf you don't have Power Query, you can follow the steps in the video people, to manually unpivot the data. The video shows how to use a pivot table trick to change a 13-column data layout into a normalized 3-column table, in a vertical structure. There are written steps and a sample file on the Fix Pivot Table Source Data page. NOTE: You can also use a macro to unpivot the source data. Video Timeline
|
Download Sample Files
|
Last updated: August 30, 2023 10:35 AM