Error Handling in Power Query (try ... otherwise)

try ... otherwise catches an error from a single expression and substitutes a fallback value instead of letting it fail the entire step — and by extension, the entire refresh.

try expression otherwise fallbackValue

There's no ribbon button for this — it's one of the things that only exists as a direct edit in the Advanced Editor or formula bar. See Power Query Editor for the UI context this fits into.

Basic Example

#"Changed Type" = Table.TransformColumns(
    Source,
    {{"OrderDate", each try Date.From(_) otherwise null, type date}}
)

"2026-01-15"   -> #date(2026,1,15)
"not a date"   -> null      <- caught, refresh continues

Without try...otherwise, a single malformed value in OrderDate fails the whole Table.TransformColumns() step, and the entire query — every row, not just the bad one — errors out.

Try typing something that isn't a date — the default 2026-01-15 works, but "not a date" (or anything else that doesn't parse) falls back to the otherwise value instead of erroring.

try Without otherwise

try Date.From([OrderDate])

Used alone, try doesn't suppress the error — it converts it into a record describing the error, rather than letting it propagate and fail the step. This is mainly useful for inspecting what actually went wrong, not for normal use in a transformation.

try Date.From("not a date")
->
[
    HasError = true,
    Error = [
        Reason = "Expression.Error",
        Message = "Couldn't convert to Date.",
        ...
    ]
]

Raising Your Own Error with error

try and otherwise only handle errors something else raises — error is how a query raises one deliberately, when a value technically converts fine but is still invalid by the query's own rules.

if [Quantity] >= 0 then [Quantity] else error "Quantity cannot be negative"

A custom error is a real, catchable error — the exact same [HasError] / [Error][Message] shape shown above works on it too, message and all.

Try a negative test value — the message you typed comes back exactly as [Error][Message], the same field try populates for a built-in error like a failed Date.From().

Checking [HasError] Explicitly

#"Added Custom" = Table.AddColumn(
    Source, "ParsedDate", each try Date.From([OrderDate])
),
#"Added Flag" = Table.AddColumn(
    #"Added Custom", "IsValid", each not [ParsedDate][HasError]
)

This pattern — keeping the full try record instead of collapsing it with otherwise — is useful when the query needs to know which rows failed, not just silently default them. A common follow-up is filtering to just the failed rows to review them, rather than losing that information the moment otherwise replaces it.

#"Failed Rows" = Table.SelectRows(#"Added Custom", each [ParsedDate][HasError])

Common Mistakes

Using try...otherwise to Hide a Problem Instead of Fixing It

each try [Amount] / [Quantity] otherwise 0

This silently turns every divide-by-zero into 0, which may or may not be the right business answer — a 0 sales-per-unit figure looks like a real, low number in a chart, not like "this row had no quantity recorded." If the fallback value itself needs to communicate "something was wrong here," a text flag or a separate boolean column communicates that more honestly than a plausible-looking number.

Wrapping an Entire Step Instead of Just the Risky Expression

try Table.TransformColumnTypes(Source, {{"OrderDate", type date}}) otherwise Source

This catches an error from any row failing, and on failure discards the entire type conversion for every row, falling back to the untouched source table. The narrower, per-value form — wrapping just the conversion inside Table.TransformColumns with an each — keeps every row that succeeds and only defaults the ones that actually fail.

Forgetting That otherwise's Value Needs to Match the Expected Type

each try Date.From([OrderDate]) otherwise "unknown"

The column ends up holding a mix of dates and the text "unknown" — the column's type can't be a clean date anymore, and anything downstream expecting a date (a date table relationship, a time-intelligence measure) breaks against those rows. null is almost always the more correct fallback for a typed column, keeping the column's type consistent even where a value is missing.

Next Steps

• M Language
• Power Query Editor
• Table.SelectRows()
• Value.Type(), Value.Is() & Comparing to null
• Table.SelectColumns() and MissingField — a different way to avoid a "column not found" error
• M Function Reference

Type conversion failing on a value that looks fine? See We Couldn't Convert to Number (or Date) for the usual root causes worth fixing before reaching for try...otherwise as a blanket catch.

Getting "We cannot convert the value null to type Table" further downstream? See that error explained — try...otherwise is one way to handle it, but finding where the null actually comes from is the real fix.

A renamed Excel sheet or an inconsistent JSON response are two of the most common real sources of that null — see Excel.Workbook() and Json.Document().