Raise and handle errors in your own functions
Write functions that reject bad input with clear messages that callers can handle.
~9 min read · advanced · Module 17: Functions, each and scope
By Tahir Nas · Examples run in our simulator · Updated
Before this: Write and use custom functions, try … otherwise
A function that receives input it can't handle has three choices: return a made-up value, return null, or raise an error. Raising a clear error is usually right inside a function, because the caller knows more about what a fallback should be.
Raising an error
error "message" raises an Expression.Error with that message. error Error.Record(reason, message, detail) lets you choose the reason too, which callers can test for:
let
ParseSku = (sku as text) as record =>
if Text.Length(sku) <> 7 or Text.At(sku, 2) <> "-"
then error Error.Record("Sku.Invalid", "SKU must look like KT-0001", sku)
else [Family = Text.Start(sku, 2), Number = Number.FromText(Text.End(sku, 4))]
in
ParseSku("KT-0042")[Number]Result
42
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
let
ParseSku = (sku as text) as record =>
if Text.Length(sku) <> 7 or Text.At(sku, 2) <> "-"
then error Error.Record("Sku.Invalid", "SKU must look like KT-0001", sku)
else [Family = Text.Start(sku, 2), Number = Number.FromText(Text.End(sku, 4))]
in
ParseSku("KT42")Result: an error
Sku.Invalid: SKU must look like KT-0001
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Callers decide
In a table, one bad SKU produces an error in one cell. The caller can catch it with try, and use the reason and message to build a review column:
let
ParseSku = (sku as text) as record =>
if Text.Length(sku) <> 7 or Text.At(sku, 2) <> "-"
then error Error.Record("Sku.Invalid", "SKU must look like KT-0001", sku)
else [Family = Text.Start(sku, 2), Number = Number.FromText(Text.End(sku, 4))],
Items = #table({"SKU"}, {{"KT-0042"}, {"KT42"}, {"RL-0007"}}),
Parsed = Table.AddColumn(Items, "Attempt", each try ParseSku([SKU])),
Result = Table.AddColumn(Parsed, "Family or problem", each if [Attempt][HasError] then [Attempt][Error][Message] else [Attempt][Value][Family], type text)
in
Table.SelectColumns(Result, {"SKU", "Family or problem"})Result
| SKU | Family or problem |
|---|---|
| KT-0042 | KT |
| KT42 | SKU must look like KT-0001 |
| RL-0007 | RL |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Good error messages
- Say what was expected, not just that something failed: SKU must look like KT-0001.
- Include the offending value in Detail, so the reader can find it.
- Use a reason with a prefix you own (Sku.Invalid, Validation.Error) so callers can tell your errors from system ones.
Use error sparingly in column-level helpers that run on every row: one malformed value then becomes one error cell, which is often right. In a validation step that checks the whole table, failing the refresh is usually the better choice, as shown in validate data and fail loudly.
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
- Returning 0 or an empty record for bad input, so problems look like real data.
- Raising errors with vague messages like 'invalid' and no value.
- Catching errors inside the function with otherwise, so callers can't tell good from bad results.
Excel, Power BI and the simulator
- Excel and Power BI Desktop show your reason and message when you click an Error cell, exactly as for built-in errors.
- A refresh that fails because of an error raised outside any cell (for example in a validation step) shows the message in Excel's Queries & Connections pane or Power BI's refresh dialog.
- The simulator keeps custom reasons, messages and detail values on raised errors.
Error.RecordText.LengthNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Functions and each quiz