Catch Error messages with TRY and CATCH in Power Query

If you have ever felt the need to peek behind the curtains of an error message but just could not for some reason. Well now you can! In this post I will try to explain how we can catch an error message and take actions based on the error messages.

 

Catch Error messages with 'TRY and CATCH' in Power Query – Video

 

Consider this 3 columnar data – Date, Units and Price

The column has a bunch of errors.

I want a clean error-free Price column. However, I want to replace the errors based on the following logic.

• Price as 10 for #N/A error 
• Price as 5 for #DIV/0! error
• Price as 0 for any other error

 

Once we load the data into Power Query, notice that the Price column has multiple errors. 

To create the New Price column, we first must identify the error type and then replace it with appropriate numeric value. In order to identify the error we first have to capture the error message.

 

Catching Error Message in Power Query

Follow these 2 simple steps:

Step 1: Add a custom column with the following code and expand it

= try [Price]

Once we expand the column, we get 3 more columns – HasError, Value and Error

 

Step 2: Expand 'Error' Column from Step 1 to get 5 columns.

Note – Every record level error has got 5 fields (columns)  – Reason, Message, Detail, Message.Format, Message.Parameters

'Message' Column is the one where we get the type of error which we use in defining our New Price Column

However, the process of retrieving the error message using the above 2 steps leads to the generation of multiple unnecessary columns. Therefore, as an alternative we can use Try and Catch in our custom column. Here's how we do it.

 

Using Try and Catch in Power Query

Now that we know where our error message resides, we can use it in our custom column. This is how my custom column formula looks like:

try [Price] catch(err) =>
if err[Message] = 'Invalid cell value '#N/A'.' then 10
else if err[Message] = 'Invalid cell value '#DIV/0!'.' then 5
else 0

Let me explain

1. Here, I have used the 'try' function on the Price Column followed by the 'catch' function that takes has 1 or no arguments.
2. If no argument is passed it behaves exactly like, try and otherwise.
3. However, if we pass 1 argument, err in our case, the function returns a record with 5 fields and stores them in the variable err.

These are the same 5 error fields mentioned in Step 2.

Passing err[Message] returns the value associated with the field 'Message' – 'Invalid cell value '#DIV/0!'.'  This is then used in an if else statement to return the desired value for the New Price column.

 

 

Recommended Readings

1. Ben’s Blog – On Error Handling in Power Query
2. Microsoft’s Blog on try and catch

 

More on Power Query:

1. IFERROR in Power Query – Try and Otherwise
2. Expand Columns to Multiple Rows by Delimiter – Power Query Challenge
3. Extract Any Step in Power Query
4. Duplicate Rows in Power Query
5. 5 Tricks to reduce steps in Power Query