Power QueryExercises

Debug a broken query step by step

Locate and fix the root cause of a failing query instead of patching the symptom.

~10 min read · intermediate · Module 20: Projects, debugging and interviews

By Tahir Nas · Examples run in our simulator · Updated

Before this: Read the M behind the Applied Steps, Find, remove and replace errors

When a query fails, the error appears at the end, but the cause is usually earlier. A fixed routine beats guessing: find the first step that's wrong, compare what it expects with what it actually receives, and fix the cause where it starts.

1. Reproduce and read the message

let
    Source = #table({"Order ID", "Qty"}, {{"A-1", "2"}, {"A-2", "5"}}),
    Typed = Table.TransformColumnTypes(Source, {{"Qty", Int64.Type}}),
    Valid = Table.SelectRows(Typed, each [OrderID] <> null)
in
    Valid

Result: an error

Expression.Error: The field 'OrderID' of the record wasn't found.

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

The field 'OrderID' of the record wasn't found. The message names the missing field.

2. Find the first failing step

Click through the steps from the top (or, in the simulator, run a single step). Source and Typed preview fine; Valid fails. The bug is in Valid or in what Valid receives.

3. Compare expectation with reality

Valid expects a column called OrderID. Ask the previous step what it actually has:

let
    Source = #table({"Order ID", "Qty"}, {{"A-1", "2"}, {"A-2", "5"}}),
    Typed = Table.TransformColumnTypes(Source, {{"Qty", Int64.Type}})
in
    Text.Combine(Table.ColumnNames(Typed), " | ")

Result

Order ID | Qty

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

The source says "Order ID" with a space. The export changed, or the step was written against a different file.

4. Fix the cause, not the symptom

Changing the filter to [#"Order ID"] works, but every later step would need the awkward name too. Renaming once, early, fixes the cause and keeps the rest of the query unchanged:

let
    Source = #table({"Order ID", "Qty"}, {{"A-1", "2"}, {"A-2", "5"}}),
    Renamed = Table.RenameColumns(Source, {{"Order ID", "OrderID"}}),
    Typed = Table.TransformColumnTypes(Renamed, {{"Qty", Int64.Type}}),
    Valid = Table.SelectRows(Typed, each [OrderID] <> null)
in
    Valid

Result

OrderIDQty
A-12
A-25

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

5. Prevent the next one

  • If the source legitimately varies, make the step tolerant on purpose (a rule-based rename) and say so in a comment.
  • If it shouldn't vary, keep the loud failure and tell the data owner.
  • Add a check (row count, required columns) where the problem would have been caught earliest.

Where to find this in Excel

Excel for Windows
Query Settings > Applied Steps: select each step in turn
What you see
The preview shows the data after the selected step; a step-level error appears in a yellow pane with its reason and message.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Applied steps (Microsoft Learn); Dealing with errors in Power Query (Microsoft Learn).

Common mistakes

  • Fixing the last step where the error surfaced instead of the first step that was wrong.
  • Deleting or wrapping the failing step in try … otherwise without understanding the cause.
  • Patching a column name in every later step instead of renaming once, early.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: a failing step shows a yellow error bar with Go To Error for cell errors; step-level errors appear when you select the step.
  • View → Query Dependencies helps when the failure comes from a referenced query rather than the one you're looking at.
  • In the simulator, the step list marks the failing step and you can preview any earlier step.
Functions in this lesson:Table.ColumnNamesTable.RenameColumns

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's free, with no account needed.

Practise: Fix a query whose steps point at the wrong step (kite shop)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.