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.
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.
-
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.
-
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.
-
Select Transform > Unpivot Columns.
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
Select Transform > Unpivot Only Selected Columns.
