Power QueryExercises

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

How to fix it

  1. Choose an aggregation such as Sum or Count in Pivot Column.
  2. Remove columns you don't want as part of the row key before pivoting.
  3. Remove duplicates first if the repeats are genuine copies.

Find the step that fails

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

Related lessons

Similar errors