New JSON theme available! Check it out here!
How can we help?

Welcome to Curbal Learning Portal. Search for answers using the search box below,
or create a request for a video or blog if you cannot find your answer.

Popular searches: DAX Glossary | Guided courses | Request a video

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:

New way of Dealing with errors in Power Query

Was this helpful?
Previous Troubleshoot Fill Up/Down cells with values above or below in Power Query | Fill up and down functions

Reader Interactions

Comments

Let us know your thoughts...

Table of Contents