try
Evaluates an expression and, instead of failing, returns a record that says whether it worked and what the value or error was.
try expressionReturns: A record with the fields HasError, Value (when it worked) and Error (when it did not).
What it does
Without try, a single bad value stops the whole step. With try, Power Query attempts the expression and returns a record instead. If it worked, HasError is false and Value holds the result. If it failed, HasError is true and Error explains why. Use it when you want to inspect failures rather than stop on them. For a simple fallback, the otherwise form is shorter.
Syntax and arguments
try expression: the expression to attempt.try expression otherwise fallback: returns the result, or the fallback when there is an error. See the otherwise page.- The record has the fields HasError, Value and Error.
Example
A list of amounts from a text export includes a few values that cannot be read as numbers. The analyst wants to see which ones failed.
let
Texts = {"1250", "480", "N/A", "75"},
Attempts = List.Transform(Texts, each try Number.FromText(_)),
Failures = List.Select(Attempts, each [HasError])
in
Failures
Each attempt is a record. Failures keeps only the records where HasError is true, so the analyst can see the bad value and the reason. The good values are in the records where HasError is false, with the number in their Value field.
Example: a second, different use
Reading the value only when it worked: if [HasError] then null else [Value] turns a try result into a value or null.
Things to watch
- Use
tryfor values you expect may fail, such as conversions. Do not wrap everything in it, or real errors are hidden. - The result is a record, not the value. Read
[Value]after checking[HasError]. trycatches errors inside the expression only. A later step can still fail.- Log the failures somewhere a reviewer can see them. A silent fallback to zero is a common source of wrong totals.
In business terms
Exports with a few bad values are normal. Catching them with try and reporting the count of failures is the controlled way to load them, because the reviewer sees exactly what was set aside.