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
ValidResult: 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.
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.
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
ValidResult
| OrderID | Qty |
|---|---|
| A-1 | 2 |
| A-2 | 5 |
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.
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.
- 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~7 minPro
Fix a query whose steps point at the wrong step (bakery)
A step was renamed and the next step still refers to the old name. Repair the chain so the query runs.
- beginner~7 minPro
Fix a query whose steps point at the wrong step (bike rental)
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.
- beginner~8 minPro
Reorder steps so cleaning happens before grouping (bakery)
The query groups regions before cleaning them, so " north" and "North" are counted separately. Fix the step order.
- beginner~8 minPro
Reorder steps so cleaning happens before grouping (bike rental)
The query groups regions before cleaning them, so " north" and "North" are counted separately. Fix the step order.