Ready to curb your Power BI skills? Find out more here!
Welcome to our Knowledge Base

Tip: Start typing in the input box for immediate search results.

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

Let us know your thoughts...

Table of Contents