You might want to unpivot data, sometimes called flattening the data, to put it in a matrix format so that all similar values are in one column. This is necessary, for example, to create a chart or a report.

Visualizing the concept of unpivoting

When you unpivot, you unpack the attribute-value pairs that represent an intersection point of the new columns and re-orient them into flattened columns:

  • Values (in blue on the left) are unpivoted into a new column (in blue on the right).

  • Attributes (in green on the left) are unpivoted into a new column (in green on the right) and duplicates are correspondingly mapped to the new Values column.

Here is the sample data used in this procedure.

Pivot Column Example

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select one or more columns. In this example, select all the columns except Country. To select more than one column contiguously or discontiguously, press Shift+Click or CTRL+Click on each subsequent column.

  3. Select Transform > Unpivot Columns.

Result

Pivot Column Result

 This command unpivots unselected columns. Use this command in a query when not all columns are known. New columns added during a refresh operation are also unpivoted.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select the columns you don't want to unpivot. To select more than one column contiguously or discontiguously, press Shift+Click or CTRL+Click on each subsequent column.

  3. Select Transform > Unpivot Other Columns.

Use this command when you don’t know the number of columns in the data source, and you want ensure the selected columns remain unpivoted after a refresh operation.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select the columns you do want to unpivot. To select more than one column contiguously or discontiguously, press Shift+Click or CTRL+Click on each subsequent column.

  3. Select Transform > Unpivot Only Selected Columns.

See Also

Power Query for Excel Help

Pivot columns 

Unpivot column (docs.com)

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Any additional feedback? (Optional)

Thank you for your feedback!

×