otherwise
Gives a fallback value to use when the expression after try fails.
try expression otherwise fallbackReturns: 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
try expression: the expression to attempt.otherwise fallback: the value to return when the attempt raises an error. It can be any value, including null.
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
- The fallback hides the error. Always count the fallbacks, or a wrong default can pass unnoticed.
- Zero is not the same as blank. Use null when the value is unknown, and zero only when zero is the real answer.
otherwiseworks only withtry. It is not a general else.- In a table step, the same idea is Table.ReplaceErrorValues, which replaces errors that already exist in a column.
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.