[Excel/Power Query] Error: Expression.Error — There weren’t enough elements in the enumeration (list length mismatch) — How to Fix It

Summary

Expression.Error: There weren’t enough elements in the enumeration appears when Power Query tries to read more items from a list than actually exist. It’s the equivalent of an “index out of range” error in programming. This typically occurs when using List.Zip, List.Combine, or list indexing like [List]{2} on a list that’s shorter than expected. To fix it, inspect list lengths with List.Count(), trim lists to consistent sizes, or wrap access in try … otherwise null for resilience.

Context

Power Query lists are dynamic, variable-length collections. Many M functions assume lists are of uniform size — for example, List.Zip pairs corresponding elements from multiple lists. If one list ends sooner than the others, Power Query throws this enumeration error. Likewise, directly indexing a list (e.g., [List]{3}) will fail when the list has fewer than four elements. The issue often arises when splitting text into lists of varying lengths, combining user inputs, or generating lists from irregular data sources like CSV rows or delimited fields.

Probable Cause

  • Out-of-range index access: Accessing {n} where the list contains fewer than n+1 items.
  • Unequal list lengths: Using List.Zip or List.Combine on lists of different sizes.
  • Empty or missing lists: One of the combined lists is null or empty.
  • Dynamic operations: Text.Split or similar functions produce lists with inconsistent element counts.
  • Conversion mismatch: Table.FromColumns built from lists of varying lengths.

Quick Fix

  1. Locate the failing step. In Power Query Editor, click through the Applied Steps until the error message appears. The error is usually tied to a list transformation or custom column formula.
  2. Inspect list contents. Drill into a problematic cell — confirm it shows a list (e.g., {1,2}) and note its element count.
  3. Check list lengths dynamically. Add a helper column:
# Add column to count list length WithCount = Table.AddColumn(Source, "ListLength", each if Value.Is([MyList], type list) then List.Count([MyList]) else null)
  1. Guard against short lists. Wrap list access in try … otherwise to prevent hard failure:
# Safe indexing Safe = Table.AddColumn(Source, "SecondItem", each try [MyList]{1} otherwise null)
  1. Trim lists before zipping or combining. Use List.FirstN() or List.Min() to equalize sizes:
# Trim lists to minimum length len1 = List.Count(ListA); len2 = List.Count(ListB); minLen = List.Min({len1, len2}); Zipped = List.Zip({List.FirstN(ListA, minLen), List.FirstN(ListB, minLen)});
  1. Normalize data generation. When producing lists (e.g., via Text.Split), handle missing delimiters:
# Ensure consistent output Split = Table.AddColumn(Source, "Parts", each let parts = Text.Split([Raw], ",") in if List.Count(parts) < 3 then List.Pad(parts, 3, null) else parts)

Full Example

Scenario 1: Uneven lists in List.Zip

# Example lists of different lengths ListA = {1,2,3}; ListB = {"A","B"};
Error: ListB too short

ErrorCase = List.Zip({ListA, ListB}); # Expression.Error

Fix: limit both lists to minimum length

minLen = List.Min({List.Count(ListA), List.Count(ListB)});
Fixed = List.Zip({List.FirstN(ListA, minLen), List.FirstN(ListB, minLen)});

Result: {{1,"A"},{2,"B"}}

Scenario 2: Direct index access

# Some rows have short lists Source = Table.FromRecords({ [ID=1, Values={10,20,30}], [ID=2, Values={40}], [ID=3, Values=null] });
Error: accessing {1} on row 2 (only 1 element)

Fail = Table.AddColumn(Source, "Second", each [Values]{1});

Fix with try/otherwise

Safe = Table.AddColumn(Source, "Second", each try [Values]{1} otherwise null);

Scenario 3: Unequal lists combined into table

# Bad: lists with different lengths A = {1,2,3}; B = {"x","y"}; # Error: BadTable = Table.FromColumns({A,B}, {"Num","Char"});
Fix:

minLen = List.Min({List.Count(A), List.Count(B)});
GoodTable = Table.FromColumns({List.FirstN(A,minLen), List.FirstN(B,minLen)}, {"Num","Char"});

Pitfalls & Debug

  • Symptom → Works on some rows but fails on others. Fix → Lists vary in length; add try … otherwise or padding logic.
  • Symptom → List.Zip throws error with mixed inputs. Fix → Equalize list lengths with List.FirstN() trimming.
  • Symptom → Table.FromColumns fails on refresh. Fix → Compute List.Min(List.Transform(lists, List.Count)) before conversion.
  • Symptom → Custom column [List]{n} errors occasionally. Fix → Wrap with try … otherwise null.
  • Symptom → Null instead of list. Fix → Add check if Value.Is([List], type list) before indexing.

Validation & Next Steps

Inspect all list-producing steps before expansions or combinations:

# Quick profiling Profile = Table.AddColumn(Source, "Len", each if Value.Is([List], type list) then List.Count([List]) else null); Stats = Table.Group(Profile, {}, {{"MinLen", each List.Min([Len]), type number}, {"MaxLen", each List.Max([Len]), type number}});
Optional: enforce consistent size

Balanced = Table.AddColumn(Source, "Padded", each if Value.Is([List], type list) then List.Pad([List], 3, null) else null)

Once stabilized, centralize your list validation logic — wrap it in a helper function (e.g., fnSafeListAccess) for reuse. For critical pipelines, assert equal list lengths early using diagnostic queries before performing joins or zips.

Sources

List.FirstN (Power Query M)

List.Zip

Value.Is

Labels

Tool/Power Query, OS/Cross-platform, Topic/List Length & Enumeration