You can pivot a column in a table by aggregating matching values in a column to create a new table orientation. The table is first sorted in ascending order by the values in the first column.
When you pivot, you take two original columns and create a new attribute-value pair that represents an intersection point of the new columns:
-
Attributes Identical values from one original attributes column (in green on the left) are pivoted into several new columns (in green on the right).
-
Values from the original unpivoted column (in blue on the left) are distributed as values to the corresponding new columns (in blue on the right).
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 the column that you want to pivot. In the example, select Date. This column's unique values become the new columns and column headers.
-
Select Transform > Pivot Column.
-
In the Pivot Column dialog box, in the Values Column list, select Amt.
-
Select Advanced options, and then select an Aggregate Value Function. In the example, select Sum.
The available options are: Don't aggregate, Count (all), Count (not blank), Minimum, Maximum, Median, Sum, and Average.
Tip If you don’t want to count a cell without a value, select Count (not blank). For example, if there ten cells in a column, and two are blank, the count is eight. -
Select OK.
Result
The Date column from the original table is pivoted to get the sum of all values from the original Amt column at the intersection of Country and each new Date column.
Here is the sample data used in this procedure.
You can pivot columns without aggregation when you're working with columns that can't be aggregated or you don't want to aggregate the values.
-
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 column that you want to pivot. In the example, select Position. This column's unique values become the new columns and column headers.
-
Select Transform > Pivot Column.
-
In the Pivot Column dialog box, in the Values Column list, select Product.
-
Select Advanced options, and then select an Aggregate Value Function. In the example, select Don't aggregate.
-
Select OK.
Result
The Position column from the original table is pivoted to get values from the original Product column at the intersection of Country and each new Position column.
In this example, we are using the example from the section, "Pivot and aggregate a column":
If you pivot the Date column, select Amt in the Values Column list, and then in the Advanced options, select Don't aggregate for an Aggregate Value Function, you get the following error message:
"Expression.Error: There were too many elements in the enumeration to complete the operation."
This error occurs because the Don't aggregate option is designed to work with a single value returned for the intersection of Country and Date, and not multiple values.
You may not see this error right away. When you originally defined it, the conditions of the data created a single value at the intersection point. Later on after a future refresh operation, the conditions of the data changed and now multiple values are possible at the intersection point. If this occurs, you can use the Pivot command with an aggregation.
