Power Query error messages: a triage map
Troubleshooting · By Tahir Nas · Examples run in our simulator · Published · Updated
Read the reason first
| Reason | Usually means | Look at |
|---|---|---|
| Expression.SyntaxError | The M can't be parsed: missing comma, bracket or quote | The line and column in the message |
| Expression.Error | Something referenced doesn't exist, or values of the wrong kind meet | Names, columns, types (field guide) |
| DataFormat.Error | A value can't be read as the requested type | The data and the culture |
| DataSource.Error / DataSource.NotFound | The source can't be reached or found | Paths, credentials, network, gateway |
| Formula.Firewall | Privacy levels block combining sources this way | Query structure and privacy settings |
Syntax errors stop everything
A syntax error means nothing ran. The message gives a position; the cause is usually just before it:
let
Source = #table({"Qty"}, {{2}})
Total = List.Sum(Source[Qty])
in
TotalResult: an error
Expression.SyntaxError: Expected 'in' (found 'Total')
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Total.Data errors live in cells
DataFormat errors start as cell errors and only stop the query when a value is needed. Count them where they're created:
let
Typed = Table.TransformColumnTypes(#table({"Amount"}, {{"12.50"}, {"twelve"}, {"7"}}), {{"Amount", type number}}, "en-US")
in
Table.RowCount(Table.SelectRowsWithErrors(Typed))Result
1
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Logic errors name the thing
Table.RenameColumns(#table({"Qty"}, {{2}}), {{"Quantity", "Units"}})Result: an error
Expression.Error: The column 'Quantity' of the table wasn't found.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Source and firewall errors
DataSource and Formula.Firewall errors come from the host's connection and privacy layers, which the simulator doesn't have (it raises UnsupportedFeature for connectors instead). In Excel or Power BI: check the path or server in Data source settings, re-enter credentials and confirm the gateway for scheduled refresh. For firewall errors, separate each source into its own staging query.
A triage routine
- Read the reason and the full message, including any named column, value or position.
- Find the first step that fails.
- Decide whether the problem is in the data (fix values or culture), the logic (fix the step) or the environment (fix the connection).
- Fix it at the source of the problem, then add a check.
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~7 minPro
Remove rows whose quantity isn't a number (kite shop)
Typing Qty as a whole number turns placeholders like n/a into errors. Remove those rows.
Related pages
Lessons
More resources
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)
- Behind the scenes of the Data Privacy Firewall (Concept, Microsoft Learn)
- List.Sum (Function reference, Microsoft Learn)
- Table.TransformColumnTypes (Function reference, Microsoft Learn)
- Table.RowCount (Function reference, Microsoft Learn)
- Table.SelectRowsWithErrors (Function reference, Microsoft Learn)
- Table.RenameColumns (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.