Home > Pivot > Create > Unpivot

Unpivot Data with Power Query

Unpivot 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 Query

Unpivot 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

• 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

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 :

• add each month to the pivot table separately
• create pivot table calculations to add up all the months.

Those steps would take time, and it would be cumbersome to work with all those fields!

Normalize Data with Unpivot

To 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:

• each sales amount is on a separate row
• All the month names are in one column
• All the sales amounts are in another column

• Note: This process is called "normalizing" the data, and you can read more about Database Normalization on Wikipedia.

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 Unpivot

You 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:

• You can continue to use your original data layout, for entering data
• The Power Query unpivoted data is connected to original data
• Any pivot table built from that unpivoted data will stay up to date with the latest entries!

Create Named Table

if 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 Unpivot

In 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:

• all the sales amounts in a single column
• all product names in a single column

For example, in the screen shot below, you can see the items from the first 3 rows, after an unpivot.

• The first order is now on 2 rows -- one for each product
• Product names are in column D
• Amounts are in column E

Start Unpivot Steps

WARNING: If your Excel data is NOT in a named table, the Unpivot steps below will convert it to a named table.

• To keep your data in its current format, make a copy of it.
• Then, use these steps on the copied data.

To start the Unpivot process with Power Query, follow these steps:

• Select any cell in the table or range of cells where the data is stored
• On the Excel Ribbon, click the Data tab
• At the left, in the Get & Transform group, click From Table/Range
  • NOTE: If your data is NOT in a named table, the Create Table dialog box will appear.
• Click OK to create the named table
• 

Power Query Editor Steps

The 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:

• default query name - thes source table name, tblSalesDE
• applied steps

In the centre, there is a grid with the data from the source table.

1) Change Query Name

First, 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 Query Settings pane, click in the Name box
• Delete the default name for the query
• Type a meaningful name for the unpivot query.

In the screen shot below, I changed the name to UnpivotSalesData

2) Delete Query Step

Next, in the Query Settings Applied Steps list, we'll delete one of the steps - Changed Type

• Click the X for the Changed Type step, to delete it.
  • Note: There is no confirmation message for the deletion.

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 Column

Next, in data table, follow the steps below, to filter the Region column, to remove the total row:

• Click drop-down arrow in the Region column heading
• In the list of region names, remove the check box for Total
• Click OK, to apply the filter

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 Column

There 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:

• In the data grid, scroll to the far right, to see the Total column
• Right-click in the heading for the Total column
• In the pop-up menu, click the Remove command

The Total column is removed, and a new step appears in the list - Removed Columns

5) Unpivot the Data

Next, follow these steps to unpivot the data

• To select the first 3 columns, click on the Region column heading, and then press Shift, and click on the Date column heading
• Next, on the Power Query Ribbon:
  1. Click the Transform tab
  2. Click the drop down arrow on the Unpivot Columns command
  3. Click the Unpivot Other Columns command

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 Columns

Next, in the revised data table, follow the steps below, to rename the two new columns:

• First, double-click the heading in the Attribute column
• Type the new name -- Product
• Next, double-click the heading in the Value column
• Type the new name -- Sales

The renamed columns are shown in the screen shot below.

A new step appears in the list - Renamed Columns

7) Detect Data Type

Next, in the revised data table, follow the steps below, to detect the data type for all of the columns:

• First, click anywhere in the data table grid
• Next, press the keyboard shortcut, Ctrl + A, to select All
• Then, on the Transform tab, click the Detect Data Type command

Power Query automatically assigns a data type to each selected column.

A new step appears in the list - Changed Type

8) Check Data Type Results

After 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:

• Any decimal values in those sales amounts would be lost. (This does not affect the source data).
• The revised data, with whole numbers, would be used for any subsequent steps in the query

To review the revised data table, follow the steps below, to check the data types:

• First, look at each column, to see which data type was assigned
• If necessary, change the data type to something different (steps below)

Change Data Type

In 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:

• Click the Calendar icon in the Date column heading
• From the list of data types, click on Date

Confirm Data Type Change

Next, a confirmation message appears, with the title, Change of Column Type, and the following message text:

• The selected column has an existing type conversion. Would you like to replace the existing conversion, or preserve the existing conversion and add the new conversion as a separate step?

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:

• Click the Replace Current button.

Because we replaced the current conversion, NO new step appears in the list

9) Load Revised Data

The final step is to load the revised data to Excel, so follow these steps:

• On the Power Query Ribbon, click the Home tab
• At the left end of the tab, click the drop down arrow for the Close & Load command
• In the drop down list, click the Close & Load To... command

The Import Data dialog box opens, with import settings that you can select.

• In the first section, Select how you want to view this data in your workbook, click the Table option.
• In the second section, Where do you want to put the data?, click the New worksheet option
• Click the OK button, to load the data to the selected location

Table With Unpivot Data

On 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 Data

If you make changes in the original data, refresh this Unpivot data, to see the updated results.

• Right-click on any cell in the table
• Click the Refresh command

Null Data in Unpivot

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.

Unpivot Source Data - Manual Steps

If 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

• 00:00 Excel Data Across Columns
• 00:26 Pivot Table Problem
• 01:12 Start Multiple Consolidation Steps
• 02:13 Unpivot the Pivot Table
• 02:46 Make Pivot Table from Unpivot Data
• 03:30 Data with Multiple Labels
• 05:35 Pivot Table from Revised Data

Download Sample Files

1. Power Query Unpivot: To test the Power Query Unpivot technique, you can download the Power Query Unpivot sample workbook. The file is in xlsx format, and is zipped. There are no macros or queries in the file.
2. Manual Unpivot: To test the manual Unpivot technique, that is shown in the video, you can get the Normalize Data for Excel Pivot Table sample workbook. The file is in xlsx format, and is zipped. There are no macros in the file.