Pivot without aggregation and the duplicate error
Diagnose and fix duplicate key/value pairs before or during a pivot.
~9 min read · advanced · Module 14: Pivot and unpivot
By Tahir Nas · Examples run in our simulator · Updated
Before this: Pivot a column with an aggregation, Check key quality before you rely on it
Pivoting attribute/value pairs back into columns is common with key-value data: one row per customer per attribute. With Don't aggregate, every cell must receive at most one value. When two rows share the same key and attribute, the cell can't hold both, so it holds an error instead.
let
Attributes = #table({"Customer", "Attribute", "Value"}, {
{"C1", "City", "Leeds"},
{"C1", "Tier", "Gold"},
{"C1", "City", "York"},
{"C2", "City", "Bath"}
}),
Pivoted = Table.Pivot(Attributes, {"City", "Tier"}, "Attribute", "Value")
in
PivotedResult
| Customer | City | Tier |
|---|---|---|
| C1 | Error: Expression.Error: There were too many elements in the enumeration to complete the operation. | Gold |
| C2 | Bath | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Find the duplicates
Group by the row key and the attribute, count, and keep counts above 1. This list tells you exactly which cells will fail:
let
Attributes = #table({"Customer", "Attribute", "Value"}, {
{"C1", "City", "Leeds"},
{"C1", "Tier", "Gold"},
{"C1", "City", "York"},
{"C2", "City", "Bath"}
}),
Counts = Table.Group(Attributes, {"Customer", "Attribute"}, {{"Values", each Text.Combine([Value], " | "), type text}, {"Rows", each Table.RowCount(_), Int64.Type}}),
Duplicates = Table.SelectRows(Counts, each [Rows] > 1)
in
DuplicatesResult
| Customer | Attribute | Values | Rows |
|---|---|---|---|
| C1 | City | Leeds | York | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Three fixes
- Fix the data: if C1 can only have one city, the source has a bad row. Remove it with a rule (latest record wins) before pivoting.
- Aggregate deliberately: for text,
each Text.Combine(_, ", ")keeps both values in one cell; for numbers, Sum or Max may be the real answer. - Add the missing key: maybe the rows differ by something you removed, such as an effective date. Keep it, and the pairs become unique.
let
Attributes = #table({"Customer", "Attribute", "Value"}, {
{"C1", "City", "Leeds"},
{"C1", "Tier", "Gold"},
{"C1", "City", "York"},
{"C2", "City", "Bath"},
{"C2", "Tier", "Silver"}
}),
Pivoted = Table.Pivot(Attributes, {"City", "Tier"}, "Attribute", "Value", each Text.Combine(_, ", "))
in
PivotedResult
| Customer | City | Tier |
|---|---|---|
| C1 | Leeds, York | Gold |
| C2 | Bath | Silver |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Where to find this in Excel
- Excel for Windows
- Transform > Pivot Column > Advanced options > Don't Aggregate
- What you see
- Don't Aggregate expects one value per cell; repeated keys produce an error in every affected cell.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Pivot columns (Microsoft Learn).
Common mistakes
- Switching the aggregation to Max or Min to hide duplicate errors.
- Assuming the pivot worked because the step shows no error; the errors are in the cells.
- Removing a column (such as a date) that made the key/value pairs unique.
Excel, Power BI and the simulator
- In Excel and Power BI Desktop, the error appears in the cell; clicking it shows There were too many elements in the enumeration to complete the operation. In Excel, loading counts it as an error row.
- The Pivot Column dialog's aggregation list doesn't include text combining; edit the formula bar to pass
each Text.Combine(_, ", "). - The simulator places the same Expression.Error in each conflicting cell.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Pivot and unpivot quiz