Skip to main
Power Query
  1. Blog
  2. M Language

New Power Query M Language keyword: catch

, Product Manager, Thursday, June 9, 2022

This content applies to: Power Query online Power Query Desktop

This article was authored by Ben Gribaudo, a community author. Please reach out to your Microsoft contact if you are interested in contributing to the Power Query Blog.

Intro from Miguel Escobar: Ben Gribaudo is best known in the Power Query community for his Power Query M Primer series published through his website (bengribaudo.com). He’s an avid Power Query user, consultant and trainer in the data analytics space that goes beyond just Power Query and we’re delighted to have him today to bring us an article on the latest keyword addition to the Power Query language: catch.

Dynamically adaptive error handling is now easier to implement in Power Query, thanks to M’s new try catch statement!

Imagine that you’re using Power Query to read data from an Excel file. A particular column of interest contains a mix of actual values as well as errors (like #REF!, #DIV/0! and #NULL!). You want to create a clean copy of this column, where errors have been replaced with appropriate fallback values—and you want to vary how these replacement values are computed depending on which error occurred.

Up until now, M has offered two options for error handling: try otherwise and plain try as showcased in the official documentation article for error handling.

try otherwise is simple but comes with the limitation that its otherwise clause has no access to the error’s specifics and so cannot factor them in—it’s a blunt “if the tried expression raises an error, switch to the otherwise.” It works great if all errors should be treated exactly the same. However, you want to change how the fallback value is computed depending on the error, so try otherwise isn’t suitable for your purposes.

In contrast, plain try outputs a record with details about the evaluation attempt, including whether an error happened and, if so the error’s specifics. Using this record, it’s possible to build dynamically adaptive error handling logic, so you can use try to achieve your goal. However, the code involved is a bit verbose—something along the lines of:

Table.AddColumn(
  Source, 
  "Clean Standard Rate",
  each 
    let
      Attempt = try [Standard Rate],
      Output = 
        if not Attempt[HasError] then Attempt[Value]
        else if Attempt[Error][Message] = "Invalid cell value '#REF!'." then [Special Rate] * 2
        else if Attempt[Error][Message] = "Invalid cell value '#DIV/0!'." then [Special Rate] / 3
        else 0
  in
    Output
)

This works, but it sure would be nice if M had built in syntax simplifying this scenario….

Meet try catch!

Now, it does! Thanks to this new language feature, the above can be simplified to:

Table.AddColumn(
  Source, 
  "Clean Standard Rate",
  each 
    try [Standard Rate]
    catch (e) =>
      if e[Message] = "Invalid cell value '#REF!'." then [Special Rate] * 2
      else if e[Message] = "Invalid cell value '#DIV/0!'." then [Special Rate] / 3
      else 0
)

The output produced is exactly the same as the preceding—but the code involved is more succinct, focused on the specifics of achieving your objective, no longer cluttered by the boilerplate code involved with using plain try.

“Old school” try is still part of the M language, but likely will be used much less now that try catch is available.

Details

Syntax Options:

try expression catch one-argument-function-definition
// example:
// try SomeFunction() catch (argName) => some-function-body
 
try expression catch zero-argument-function-definition
// example: 
// try SomeFunction() catch () => some-function-body

For both versions, the behavior is the same: If the tried expression returns a value, that value is returned; if, instead, the expression raises an error, the catch function is invoked. The difference between the two is that the one-parameter catch function version is passed the error that occurred, allowing that function to factor in the error’s details. In contrast, the zero-parameter catch function isn’t passed the error’s details, making its use effectively equivalent to try otherwise.

There are a few interesting twists to catch’s syntax:

  • The catch function must be defined inline, so a reference to a function cannot be used.
  • each cannot be used to define the catch function.
  • Also, that function’s definition cannot include any type constraints.

Based on these rules, the below examples are invalid. 

// invalid - catch attempts to use a function value reference instead of defining the function immediately after catch
let
  ErrorHandler = (e) => ...,
  Result = try Something() catch ErrorHandler
in
  Result

// invalid – type constraints not allowed here
try Something() catch (e as record) as any => . . .
// invalid – each not allowed here
try Something() catch each ...

Further Reading by Ben