The 2nd Edition of #25daysofdaxfridays challenge is 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?

Reader Interactions

Comments

Leave a Reply to Ruth Pozuelo Martinez Cancel reply

Table of Contents