try … catch in Power Query M: the newer error-handling syntax
Explainer · By Tahir Nas · Examples run in our simulator · Updated
8 min read
try … catch is the form of Power Query's error handling that hands you the error itself: try expression catch (e) => … evaluates the expression and, only if it raises an error, calls your function with the error record so you can decide what to return. It does everything try … otherwise does, plus it lets you read the error's reason, message and detail, treat different errors differently and pass on the ones you can't fix.
The forms of try
Microsoft's M language specification on error handling defines one try expression with an optional handler. That gives the forms you will see in real queries, with catch in two variants:
| Form | What you get back |
|---|---|
try x | A record: [HasError = false, Value = …] or [HasError = true, Error = …] |
try x otherwise y | The value of x, or y if x raised an error |
try x catch (e) => f(e) | The value of x, or whatever your function returns from the error record e |
try x catch () => y | Same as otherwise: y if x raised an error |
The handler only runs if the protected expression fails, and an error raised inside the handler is not caught by the same try; it propagates. Microsoft's Power Query error handling article notes that the catch keyword was added to Power Query in May 2022, which is why older queries and many forum answers only use otherwise.
If you haven't used try … otherwise yet, the lesson on try … otherwise starts there, and the glossary entry for the error value explains what an error in a cell actually is.
Start with the record that try returns
try on its own is the clearest way to see what catch will receive. Every example in this post was run in the site's M simulator, and the results quoted are the ones it returned. Here, sensor readings arrive as text and one of them isn't a number:
let
Source = #table(type table [Sensor = text, Reading = text], {
{"A1", "21.5"}, {"A2", "n/a"}, {"A3", "19"}
}),
Checked = Table.AddColumn(Source, "Check", each try Number.FromText([Reading], "en-GB")),
Expanded = Table.ExpandRecordColumn(Checked, "Check", {"HasError", "Value"})
in
ExpandedResult
| Sensor | Reading | HasError | Value |
|---|---|---|---|
| A1 | 21.5 | false | 21.5 |
| A2 | n/a | true | null |
| A3 | 19 | false | 19 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Rows A1 and A3 come back with HasError false and their numbers; A2 has HasError true and a null value. Had you also expanded Error, you would see a record with fields such as Reason, Message and Detail. The specification lists the full canonical shape, which also includes Message.Format, Message.Parameters and ErrorCode, all optional.
catch (e) => is this same Error record handed straight to your function, without the expand step.
catch with a parameter: use the error
The most useful pattern is to keep the number when conversion works and record why it failed when it doesn't. Here the catch function returns the error record itself, so one helper column holds either the number or the error, and two short steps split it:
let
Source = #table(type table [Sensor = text, Reading = text], {
{"A1", "21.5"}, {"A2", "n/a"}, {"A3", "19"}, {"A4", "1O"}
}),
Checked = Table.AddColumn(Source, "Checked",
each try Number.FromText([Reading], "en-GB") catch (e) => e),
Value = Table.AddColumn(Checked, "Value",
each if [Checked] is record then null else [Checked], type nullable number),
Problem = Table.AddColumn(Value, "Problem",
each if [Checked] is record then [Checked][Message] else null, type nullable text),
Result = Table.RemoveColumns(Problem, {"Checked"})
in
ResultResult
| Sensor | Reading | Value | Problem |
|---|---|---|---|
| A1 | 21.5 | 21.5 | null |
| A2 | n/a | null | We couldn't convert to Number. |
| A3 | 19 | 19 | null |
| A4 | 1O | null | We couldn't convert to Number. |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The conversion runs inside the try itself, which matters: wrapping a record such as [Value = Number.FromText(…)] in try would not catch anything, for the reason explained in the section on where try doesn't reach.
A1 and A3 parse to 21.5 and 19. A2 ("n/a") and A4 ("1O", a letter O typed for a zero) get a null value and the message "We couldn't convert to Number." in the Problem column. You now have clean numbers to load and a column that tells someone exactly which rows to fix at the source, which beats silently replacing them. Removing error rows instead is one of the ways rows go missing without a warning. The exercise to parse sensor readings safely with try … otherwise is the same job, and the DataFormat.Error: We couldn't convert to Number page covers the usual causes of that message.
catch without a parameter is just otherwise
When you don't need the error, a zero-parameter catch and otherwise are interchangeable. The specification calls the catch form "a longer, alternative syntax" for otherwise:
let
WithOtherwise = try error "A" otherwise 1,
WithCatch = try error "A" catch () => 1
in
#table({"otherwise", "catch ()"}, {{WithOtherwise, WithCatch}})Result
| otherwise | catch () |
|---|---|
| 1 | 1 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Both columns return 1. For a plain fallback, pick one of the two and use it consistently; otherwise is shorter.
Handle the errors you expect, re-raise the rest
Replacing every error with null hides problems you didn't plan for. A better rule is to catch the error you expect and let anything else through. The error record's Reason makes this easy, and error e raises the same error again:
let
ToNumber = (t) =>
try Number.FromText(t, "en-GB")
catch (e) => if e[Reason] = "DataFormat.Error" then null else error e,
Source = #table(type table [Reading = text], {{"12.5"}, {"oops"}, {"7"}}),
Result = Table.AddColumn(Source, "Value", each ToNumber([Reading]), type nullable number)
in
ResultResult
| Reading | Value |
|---|---|
| 12.5 | 12.5 |
| oops | null |
| 7 | 7 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
"oops" is a format problem, so it becomes null and the other readings parse to 12.5 and 7. But if something unexpected reaches the function, such as a number instead of text, the error isn't a DataFormat.Error and the handler passes it on:
let
ToNumber = (t) =>
try Number.FromText(t, "en-GB")
catch (e) => if e[Reason] = "DataFormat.Error" then null else error e
in
ToNumber(42)Result: an error
Expression.Error: We cannot convert the value of type Number to type Text.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
This raises an Expression.Error, because Number.FromText expects text. That's the behaviour you want: a wrong type coming in from an upstream change should stop the refresh and say so, not turn into a quiet null. The lesson on validating data and failing loudly builds on this idea.
Raise your own errors and catch them with detail
Your own functions can raise structured errors with Error.Record, which takes a reason plus an optional message, detail value, parameters and error code. When the message needs values from the data, building it with Text.Format keeps it readable. The detail can be a record carrying the data that caused the problem, and catch can read it back:
let
CheckQty = (q as number) =>
if q < 0
then error Error.Record("Validation", "Quantity can't be negative", [Quantity = q])
else q,
Source = #table(type table [OrderId = number, Qty = number], {{1, 3}, {2, -1}, {3, 5}}),
Checked = Table.AddColumn(Source, "Result",
each try Text.From(CheckQty([Qty]))
catch (e) => e[Reason] & ": " & e[Message] & " (" & Text.From(e[Detail][Quantity]) & ")",
type text)
in
CheckedResult
| OrderId | Qty | Result |
|---|---|---|
| 1 | 3 | 3 |
| 2 | -1 | Validation: Quantity can't be negative (-1) |
| 3 | 5 | 5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Orders 1 and 3 show 3 and 5. Order 2 shows "Validation: Quantity can't be negative (-1)". Using your own reason name, here "Validation", means a catch elsewhere can tell your errors apart from the engine's. The lesson on raising and handling errors in your own functions goes further with this. The post on recursive functions in M uses the same kind of structured error to report a loop in a management chain.
Where try … catch doesn't reach
The handler only sees errors raised while the protected expression is being evaluated. M is lazy, so a value can leave the try unevaluated and fail later. The specification gives this example: a function returns a record whose field a is an error, try wraps the call, and the error still escapes when the field is read.
let
f = (x) => [a = error "bad", b = x],
g = try f(42) otherwise 123
in
g[a]Result: an error
Expression.Error: bad
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The query fails with the message "bad". The record itself was built without error, so try had nothing to catch; the field was evaluated only when g[a] read it, outside the try. Move the try to the expression that actually produces the error, try f(42)[a] otherwise 123, and it returns 123. The same applies to lists and tables: wrap the step or the cell that fails, not a function that merely returns something that will fail later.
Errors in cells versus errors in steps
A cell-level error, like a bad reading in one row, is handled inside the column's function as above. A step-level error, such as a missing column or a file that can't be opened, stops the whole query and needs either a try around that step or a fix to the step. For missing columns, handling changing column headers usually fixes the step itself. The lesson on finding, removing and replacing errors covers the table-wide tools (Table.ReplaceErrorValues and Table.RemoveRowsWithErrors), and the error messages triage map helps you work out which kind you have.
Choosing between otherwise and catch
| You want to | Use |
|---|---|
| Replace any error with a fixed value | try x otherwise y |
| Fall back to another column | try [Standard Rate] otherwise [Special Rate] |
| Record the error message for review | try x catch (e) => e[Message] |
| Replace one kind of error and keep the rest | try x catch (e) => if e[Reason] = "…" then … else error e |
| Read data you attached to your own error | try x catch (e) => e[Detail] |
Both forms are fine to mix in one model, but in any single function a reader should be able to see at a glance which errors are being swallowed. For practice, the errors and validation exercises on Power Query Exercises check each answer against the expected table.
Frequently asked questions
Does try … catch work in Excel and Power BI?
Yes. It's part of the M language, which both hosts run. Microsoft dates the catch keyword to May 2022, so a copy of Power Query older than that won't recognise it. In that case try … otherwise, or try x followed by reading [Error], does the same job.
Can the catch function take two parameters?
No. The specification's grammar allows a catch function with zero parameters or one; the one parameter receives the error record.
What is the difference between e[Reason] and e[Message]?
Reason is the error's category, such as DataFormat.Error or Expression.Error. Message is the human-readable text. Branch on Reason when you can, because a short category name is easier to match exactly than a sentence.
Sources
- M language specification on error handling (learn.microsoft.com)
- Power Query error handling article (learn.microsoft.com)
- Error.Record (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- List.Generate in Power Query: loops without the confusionHow List.Generate works in Power Query M: the four functions, record state, the selector, the off-by-one trap and loops for dates, balances and paging.
- Recursive functions in Power Query M with the @ operatorRecursive functions in Power Query M: why the spec needs @ for a function to call itself, a no-@ alternative, a management chain and loop guards.
- Table.Buffer and List.Buffer: when buffering helps and when it hurtsWhen Table.Buffer and List.Buffer make Power Query faster or more predictable, when they slow it down by blocking query folding, with tested M examples.
Practise Power Query in the browser with graded exercises checked against fresh data.
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.