Power QueryExercises

Power Query error messages: a triage map

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

Jump to the practice tasks

Read the reason first

ReasonUsually meansLook at
Expression.SyntaxErrorThe M can't be parsed: missing comma, bracket or quoteThe line and column in the message
Expression.ErrorSomething referenced doesn't exist, or values of the wrong kind meetNames, columns, types (field guide)
DataFormat.ErrorA value can't be read as the requested typeThe data and the culture
DataSource.Error / DataSource.NotFoundThe source can't be reached or foundPaths, credentials, network, gateway
Formula.FirewallPrivacy levels block combining sources this wayQuery 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
    Total

Result: 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.

The comma after the Source step is missing, so the parser stops at 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.

The column 'Quantity' of the table wasn't found. The rename names a column that doesn't exist.

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

  1. Read the reason and the full message, including any named column, value or position.
  2. Find the first step that fails.
  3. Decide whether the problem is in the data (fix values or culture), the logic (fix the step) or the environment (fix the connection).
  4. 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.

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.