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: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