try … otherwise
Catch conversion errors inside an expression with a sensible fallback.
~8 min read · intermediate · Module 8: Profiling, errors and validation
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Find, remove and replace errors
try expression otherwise fallback evaluates the expression. If it raises an error, you get the fallback instead. It's the M equivalent of Excel's IFERROR, but it works inside any expression, not just on whole cells.
let
Log = #table({"Sensor", "Raw"}, {{"S-1", "21.5"}, {"S-2", "ERR"}, {"S-3", "19"}, {"S-4", "OFFLINE"}}),
Parsed = Table.AddColumn(Log, "Reading", each try Number.FromText([Raw], "en-US") otherwise null, type number)
in
ParsedResult
| Sensor | Raw | Reading |
|---|---|---|
| S-1 | 21.5 | 21.5 |
| S-2 | ERR | null |
| S-3 | 19 | 19 |
| S-4 | OFFLINE | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The advantage over if [Raw] = "ERR" then null else … is that you don't need to know every fault code. OFFLINE wasn't in anyone's list, and it's handled anyway.
Inspecting the error
Without otherwise, try returns a record. HasError says whether the expression failed; when it did, the Error field holds a record with Reason, Message and Detail. Keeping the reason helps whoever fixes the source:
let
Log = #table({"Sensor", "Raw"}, {{"S-1", "21.5"}, {"S-2", "ERR"}}),
Tried = Table.AddColumn(Log, "Attempt", each try Number.FromText([Raw], "en-US")),
Flags = Table.AddColumn(Tried, "Problem", each if [Attempt][HasError] then [Attempt][Error][Reason] else null, type text)
in
Table.SelectColumns(Flags, {"Sensor", "Problem"})Result
| Sensor | Problem |
|---|---|
| S-1 | null |
| S-2 | DataFormat.Error |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Keep try narrow
Wrap only the expression that can legitimately fail. try ([Qty] * Number.FromText([Price])) otherwise 0 also hides a misspelled column name or a null Qty, because those are errors too. A narrow try catches the conversion problem you expect and lets genuine bugs surface.
try also works on whole steps, which is useful when a source might not exist yet: try Source otherwise #table({"Reading"}, {}) returns an empty table with the right column instead of failing. Use that sparingly, and only where an empty result is genuinely acceptable.
Where to find this in Excel
There's no dedicated button. Write the expression in a custom column (Add Column > Custom Column) or edit the step's M in the Advanced Editor (View > Advanced Editor).
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Add a custom column (Microsoft Learn); Use Power Query to transform data (the Power Query editor) (Microsoft Learn).
Common mistakes
- Wrapping a whole calculation in try, which hides typos in column names.
- Falling back to 0 for a measurement, inventing data.
- Using a text fallback in a number column, so the column type becomes any.
Excel, Power BI and the simulator
- The editor doesn't generate try … otherwise; you write it in Add Column → Custom Column or in the formula bar, in both Excel and Power BI Desktop.
- Newer hosts also support
try … catch (e) => …, which passes the error record to a function. The simulator supportstry … otherwiseand the baretryrecord shown here. - Error records in the simulator have Reason, Message and Detail fields like native ones.
Number.FromTextNext step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Parse sensor readings safely with try … otherwise (greenhouse)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- intermediate~8 minPro
Parse sensor readings safely with try … otherwise (greenhouse)
Add a numeric Reading column that is null whenever Raw isn't a number.
- intermediate~8 minPro
Parse sensor readings safely with try … otherwise (cold store)
Add a numeric Reading column that is null whenever Raw isn't a number.
- advanced~10 minPro
Classify quantities as OK, Missing, Invalid or Negative (warehouse)
Give every stock count a validation status so the warehouse knows which rows to recount.
- advanced~10 minPro
Classify quantities as OK, Missing, Invalid or Negative (pantry)
Give every stock count a validation status so the warehouse knows which rows to recount.
- advanced~10 minPro
Parse dates that arrive in two different formats (salon)
Bookings from the website use 2026-03-04; bookings from the phone team use 04/03/2026 (day first). Parse both correctly.
- advanced~10 minPro
Parse dates that arrive in two different formats (boat hire)
Bookings from the website use 2026-03-04; bookings from the phone team use 04/03/2026 (day first). Parse both correctly.