Power QueryExercises

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
    Parsed

Result

SensorRawReading
S-121.521.5
S-2ERRnull
S-31919
S-4OFFLINEnull

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

SensorProblem
S-1null
S-2DataFormat.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 supports try … otherwise and the bare try record shown here.
  • Error records in the simulator have Reason, Message and Detail fields like native ones.
Functions in this lesson:Number.FromText

Next 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.