Power QueryExercises

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
    Pivoted

Result

CustomerCityTier
C1Error: Expression.Error: There were too many elements in the enumeration to complete the operation.Gold
C2Bathnull

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Only C1's City cell fails. The query itself runs, so the error surfaces later, when the cell is loaded or used.

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
    Duplicates

Result

CustomerAttributeValuesRows
C1CityLeeds | York2

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
    Pivoted

Result

CustomerCityTier
C1Leeds, YorkGold
C2BathSilver

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Combining text is honest about the conflict; it doesn't pick a winner silently.

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