try … otherwise
M's error handling: try runs an expression and otherwise supplies a fallback if it errors.
try without otherwise returns a record with HasError and Error fields, which is useful for logging why a value failed. Catching every error with a default can hide real data problems, so keep the original value visible or count the replaced rows.
In the Power Query editor
Written in a Custom Column or the formula bar; the editor has no dialog for it.
Common mistake
Wrapping a whole step, or a whole table, in try … otherwise so that any failure returns a default. That hides the difference between a value that is genuinely missing and one that failed for a reason you would want to fix, such as a changed column name. Keep try around the single conversion that can fail, keep the original text in another column while you test, and count how many rows took the otherwise branch so a sudden jump is visible.
Learn more
Practise it
- 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.
- 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
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.