Downloads
Power Query MLanguageBeginner

otherwise

Gives a fallback value to use when the expression after try fails.

Syntax
try expression otherwise fallback

Returns: The result of the expression if it works, or the fallback if it raises an error.

What it does

otherwise is the second half of the safe form try ... otherwise. It says what to use when the expression fails. Use it for a clear fallback, such as null for a blank entry or zero for a missing rate. It is the most common way to convert a column that has a few bad values without stopping the refresh.

Syntax and arguments

Example

A column of amounts from a text export holds numbers, a few blanks and a few words such as "pending". The query converts what it can and sets the rest to null, so the total works.

let
    Texts = {"1250", "pending", "480", "75"},
    Converted = List.Transform(Texts, each try Number.FromText(_) otherwise null),
    Total = List.Sum(List.RemoveNulls(Converted))
in
    Total

Converted is {1250, null, 480, 75}, and the total is 1805. The word "pending" is set aside as null, and the count of nulls can be reported as a control.

Example: a second, different use

Using a default rate when a lookup fails: try Rates[USD] otherwise 1 falls back to 1 if the currency is not in the rate table.

Things to watch

In business terms

Setting bad values aside with a visible fallback is the standard way to load messy exports. Report how many values fell back, so the reviewer knows what was not converted.

Related functions

All Power Query M functions · View as Markdown · M basics