Resolve Expression.Error in Power Query: a field guide
Troubleshooting · By Tahir Nas · Examples run in our simulator · Published · Updated
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.
"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
TotalResult: 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.
"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 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
| Qty | Value |
|---|---|
| 2 | Error: 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.
"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
- Find the first step that fails, not the last one that shows the error.
- Ask what that step expects (column, name, position, type) and look at what it actually receives.
- Fix the cause where it starts: rename early, correct the reference, convert the type.
- 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.
- beginner~7 minFree
Fix a query whose steps point at the wrong step (kite shop)
A step was renamed and the next step still refers to the old name. Repair the chain so the query runs.
- beginner~8 minFree
Reorder steps so cleaning happens before grouping (kite shop)
The query groups regions before cleaning them, so " north" and "North" are counted separately. Fix the step order.
Related pages
Lessons
More resources
Error fixes
Sources
Microsoft's official documentation for the features and functions used on this page.
- Dealing with errors in Power Query (Concept, Microsoft Learn)
- Error handling (M language specification) (Concept, Microsoft Learn)
- Table.SelectColumns (Function reference, Microsoft Learn)
- List.Sum (Function reference, Microsoft Learn)
- Text.From (Function reference, Microsoft Learn)
- Table.AddColumn (Function reference, Microsoft Learn)
- Text.Split (Function reference, Microsoft Learn)
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.