Expression.Error: There were too many elements in the enumeration to complete the operation
By Tahir Nas · Examples run in our simulator · Updated
Expression.Error: There were too many elements in the enumeration to complete the operation.
Usually raised by Pivot Column with "Don't aggregate": two source rows land in the same output cell, and without an aggregation M can't put two values in one cell. The error appears in those cells.
Common causes
- Pivoting without aggregation when a row key and attribute repeat.
- Extra columns in the pivot input that aren't part of the intended row key.
- Duplicate source rows.
How to fix it
- Choose an aggregation such as Sum or Count in Pivot Column.
- Remove columns you don't want as part of the row key before pivoting.
- Remove duplicates first if the repeats are genuine copies.
Find the step that fails
- Open the query in the Power Query editor (Excel: Data → Queries & Connections → Edit; Power BI Desktop: Transform data).
- Click the steps in Applied Steps from the top. The first step that shows the error is where it starts; later steps only repeat it.
- Read that step's formula in the formula bar (View → Formula Bar if it's hidden) and compare it with the columns and values the previous step produced.
Minimal example
Raises the error:
Table.Pivot(#table({"Region", "Month", "Units"}, {{"North", "Jan", 3}, {"North", "Jan", 2}}), {"Jan"}, "Month", "Units"){0}[Jan]Fixed:
Table.Pivot(#table({"Region", "Month", "Units"}, {{"North", "Jan", 3}, {"North", "Jan", 2}}), {"Jan"}, "Month", "Units", List.Sum){0}[Jan]Both snippets are run by the Power Query Exercises simulator on every build: the first must raise Expression.Error, the second must run.
Practise avoiding it
- advanced~10 minPro
Pivot order statuses into columns (kite shop)
Count orders per region with one column per status (Complete, Cancelled, Pending).
- intermediate~9 minPro
Pivot months into columns, summing repeated entries (bakeries)
Each store can have several sales lines per month. Pivot Month into columns with the total Sales per store and month.
- intermediate~8 minPro
Turn attribute–value rows into one row per record (intake)
A form tool stores answers as (RecordID, Field, Value) rows. Pivot Field so each record becomes one row.
- advanced~12 minPro
Pivot categories and add a total that includes future columns (consultancy)
Pivot expense categories into columns (summing amounts), then add a Total across every category column, including new ones.