Downloads
Power Query MLanguageIntermediate

try

Evaluates an expression and, instead of failing, returns a record that says whether it worked and what the value or error was.

Syntax
try expression

Returns: 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

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

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.

Related functions

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