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
CheckedResult: 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.
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
SummaryResult
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?
| Situation | Better choice |
|---|---|
| Numbers will be published or paid out | Fail the refresh |
| A dashboard can show partial data with a warning | Flag the rows and count them |
| The problem is common and known | Clean 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.
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