Power QueryExercises

Resolve Expression.Error in Power Query: a field guide

Troubleshooting · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

Expression.Error is the broadest error reason in M. Read the rest of the message: it nearly always names the missing thing. Below are the variants you'll meet most, each reproduced in the simulator, with the usual fix.

"The column 'X' of the table wasn't found"

A step names a column that doesn't exist at that point. Usual causes: the export renamed a column, headers weren't promoted, a different capitalisation, or an earlier step removed it.

Table.SelectColumns(#table({"OrderId", "Qty"}, {{"A-1", 2}}), {"OrderID", "Qty"})

Result: an error

Expression.Error: The column 'OrderID' of the table wasn't found.

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

OrderId vs OrderID. Fix the name, or rename the source column once, early.

"The name 'X' wasn't recognized"

A step or query name is misspelled, has the wrong case, or refers to a step that was renamed or deleted.

let
    Source = #table({"Qty"}, {{2}}),
    Total = List.Sum(#"Changed Type"[Qty])
in
    Total

Result: an error

Expression.Error: The name 'Changed Type' wasn't recognized. Make sure it's spelled correctly.

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

There's no step called Changed Type any more. Point the reference at the step that exists.

"We cannot apply operator & to types Text and Number"

An operator got the wrong kinds of values: text joined to a number, a number added to text, a date compared with text. Convert one side explicitly.

let
    Row = [OrderID = "A-1", Qty = 2]
in
    Row[OrderID] & " x" & Text.From(Row[Qty])

Result

A-1 x2

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

The fixed version: Text.From turns the number into text before joining.

"The field 'X' of the record wasn't found"

Inside each, [X] reads a field of the current row. If the column doesn't exist, you get this record-level message instead of the table-level one. Same causes, same fixes.

Table.AddColumn(#table({"Qty"}, {{2}}), "Value", each [Qty] * [Price])

Result

QtyValue
2Error: Expression.Error: The field 'Price' of the record wasn't found.

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

The step runs, but every cell in the new column holds the error, and it surfaces when the value is used.

"There weren't enough elements in the enumeration"

Positional access past the end of a list or table: {3} on a three-item list (positions 0–2), or Table{0} on an empty table. Use ? for optional access, or check the count first.

let
    Parts = Text.Split("Leeds", "-")
in
    Parts{1}? ?? "no suffix"

Result

no suffix

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

"The key didn't match any rows in the table"

A lookup like Source{[Item = "Orders"]} found no row with that key, typically a renamed sheet or table in a workbook. If several rows match, a similar error says the key matched more than one row.

A routine that works for all of them

  1. Find the first step that fails, not the last one that shows the error.
  2. Ask what that step expects (column, name, position, type) and look at what it actually receives.
  3. Fix the cause where it starts: rename early, correct the reference, convert the type.
  4. Add a check so the next schema change fails loudly at the start of the query.

Practise it in the simulator

Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. Free tasks need no account.

Lessons

More resources

Error fixes

Sources

Microsoft's official documentation for the features and functions used on this page.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.