Print

New way of Dealing with errors in Power Query

There is now a new way (Released in May 2020) to manage errors with more granularity in Power Query.

Before you could use try otherwise to capture the error and convert it to null or something else:

= Table.AddColumn(#"Expanded Error", "Custom", 
each try [#"a/B"] otherwise null)

but with the new keyword catch, you can now specify how to treat each error.
In the example below we will substitute the error #REF! by Column A and error #DIV0! by 0, other errors will be converted to nulls:

= Table.AddColumn(#"Added Custom1", "Custom.1",
 each try [#"a/B"] catch(f)=> 
    if f[Message] = "Invalid cell value '#REF!'." then [Value A] else 
    if f[Message] = "Invalid cell value '#DIV/0!'." then 0
      else null)

Table of contents:

00:00 Intro
00:50 Replace all errors by null in same column
01:08 Replace all errors by null in new column with if otherwise
02:30 Replace all errors differently with catch keyword

Watch the tutorial:

Catching errors in HTTP / API calls in Power Query with try catch method

Was this helpful?

Reader Interactions

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Table of Contents