Access to this page requires authorization. You can try
signing in
or
changing directories
.
Access to this page requires authorization. You can try
changing directories
.
In Power Query, you can create a table that contains an aggregate value for each unique value in a column. Power Query groups each unique value, does an aggregate calculation for each value, and pivots the column into a new table.
Diagram showing the left table with a blank column and rows. An Attributes column contains nine rows with A1, A2, and A3 repeated three times. A Values column contains, from top to bottom, values V1 through V9. With the columns pivoted, the right table contains a blank column and rows. The Attributes values A1, A2, and A3 are column headers. The A1 column contains the V1, V4, and V7 values. The A2 column contains the V2, V5, and V8 values. Finally, the A3 column containing the V3, V6, and V9 values.
Imagine a table like the one in the following image.
Table containing a Country column set as the Text data type, a Date column set as the Data data type, and a Value column set as the Whole number data type. The Country column contains USA in the first three rows, Canada in the next three rows, and Panama in the last three rows. The Date column contains a date in the first, forth, and seventh rows, a second date in the second, fifth, and eighth rows, and third date in the third, sixth, and ninth rows.
This table contains values by country and date in a simple table. In this example, you want to transform this table into the one where the date column is pivoted, as shown in the following image.
Table containing a Country column set in the Text data type, and a first, second, and third date columns set as the Whole number data type. The Country column contains Canada in row 1, Panama in row 2, and USA in row 3.
During the pivot columns operation, Power Query sorts the table based on the values found on the first column—at the left side of the table—in ascending order.
To pivot a column
Select the column that you want to pivot. In this example, select the
Date
column.
On the
Transform
tab of the
Any column
group, select
Pivot column
.
You can pivot columns without aggregating when you're working with columns that can't be aggregated, or aggregation isn't required for what you're trying to do. For example, imagine a table like the following image, that has
Country
,
Position
, and
Product
as fields.
Table with Country column containing USA in the first three rows, Canada in the next three rows, and Panama in the last three rows. The Position column contains First Place in the first, fourth, and seventh rows, Second Place in the second, fifth, and eighth rows, and third Place in the third, sixth, and ninth rows.
Let's say you want to pivot the
Position
column in this table so you can have its values as new columns. For the values of these new columns, you use the values from the
Product
column. Select the
Position
column, and then select
Pivot column
to pivot that column.
Table containing Country, First Place, Second Place, and Third Place columns, with the Country column containing Canada in row 1, Panama in row 2, and USA in row 3.
Errors when using the Don't aggregate option
The way the
Don't aggregate
option works is that it grabs a single value for the pivot operation to be placed as the value for the intersection of the column and row pair. For example, let's say you have a table like the one in the following image.
Table with a Country, Date, and Value columns. The Country column contains USA in the first three rows, Canada in the next three rows, and Panama in the last three rows. The Date column contains a single date in all rows. The value column contains various whole numbers between 20 and 785.
You want to pivot that table by using the
Date
column, and you want to use the values from the
Value
column. Because this pivot makes your table have just the
Country
values on rows and the
Dates
as columns, you get an error for every single cell value because there are multiple rows for every combination of
Country
and
Date
. The outcome of this
Pivot column
operation yields the results shown in the following image.
Power Query editor pane showing a table with Country and date value columns. The Country column contains Canada in the first row, Panama in the second row, and USA in the third row. All of the rows under the date value column contain Errors, with one error selected. Under the table is another pane that shows the expression error with the "There are too many elements in the enumeration to complete the operation" message.
Notice the error message
Expression.Error: There were too many elements in the enumeration to complete the operation.
This error occurs because the
Don't aggregate
operation only expects a single value for the country and date combination.
