Power QueryExercises

Validate data and fail loudly

Stop a refresh with a clear message when data breaks a business rule.

~10 min read · advanced · Module 8: Profiling, errors and validation

By Tahir Nas · Examples run in our simulator · Updated

Before this: try … otherwise, Profile columns before you trust them

Some problems should stop the refresh. If a finance extract has a negative quantity or a missing currency, publishing a wrong number is worse than publishing nothing. M lets you check a rule and raise your own error with a message that tells the reader exactly what's wrong.

Check a rule across all rows

Table.MatchesAllRows(table, condition) returns true when every row satisfies the condition. Combined with error, it becomes a gate:

let
    Source = #table({"Line", "Qty", "Currency"}, {{1, 2, "EUR"}, {2, -1, "EUR"}, {3, 4, null}}),
    Checked = if Table.MatchesAllRows(Source, each [Qty] > 0 and [Currency] <> null)
        then Source
        else error Error.Record("Validation.Error", "Some lines have a non-positive quantity or no currency.")
in
    Checked

Result: an error

Validation.Error: Some lines have a non-positive quantity or no currency.

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

The refresh fails with your reason and message, which is what the person running it sees.

When the data is clean, the same query passes the table through unchanged, so the check costs nothing downstream.

Tell people which rows

A message is more useful with the offending rows in it. Count or list them in the message, or keep a separate check query that returns the bad rows for review:

let
    Source = #table({"Line", "Qty", "Currency"}, {{1, 2, "EUR"}, {2, -1, "EUR"}, {3, 4, null}}),
    Bad = Table.SelectRows(Source, each not ([Qty] > 0 and [Currency] <> null)),
    Summary = Text.Format("#{0} invalid lines: #{1}", {Text.From(Table.RowCount(Bad)), Text.Combine(List.Transform(Bad[Line], Text.From), ", ")})
in
    Summary

Result

2 invalid lines: 2, 3

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

Use the summary text as the message of the error in the gate, and readers know exactly where to look.

Fail or flag?

SituationBetter choice
Numbers will be published or paid outFail the refresh
A dashboard can show partial data with a warningFlag the rows and count them
The problem is common and knownClean or map it, and report the count

Where to find this in Excel

There's no button for this. In Excel you write it in the Power Query editor's Advanced Editor (View > Advanced Editor, also on the Home tab), which shows the whole query as M.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Use Power Query to transform data (the Power Query editor) (Microsoft Learn).

Common mistakes

  • Silently filtering out invalid rows in a finance query, so totals are wrong without anyone knowing.
  • Raising a generic error message that doesn't say which rule or which rows failed.
  • Forgetting that conditions on null evaluate to null, which slips past both the check and the bad-row list.

Excel, Power BI and the simulator

  • In Excel, a custom error stops the query's load and shows the message in the Queries & Connections pane. In Power BI Desktop, the refresh dialog shows it; in the service, the refresh history does.
  • Validation queries that should never be loaded can be connection-only (Excel) or have Enable load turned off (Power BI).
  • The simulator raises custom errors with the reason and message you give, and supports Table.MatchesAllRows and Table.MatchesAnyRows.
Functions in this lesson:Table.MatchesAllRowsError.RecordTable.SelectRows

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Errors and validation quiz