IFERROR In Power Query – Try And Otherwise

In this post, I am going to talk about the IFERROR function in Power Query. There is no explicit IFERROR function in Power Query but we have try and otherwise statement which works similar to the IFERROR function in Excel.

 

IFERROR in Power Query (try and otherwise) – Video

 

Power Query try and otherwise – Example 1

1. Consider this sample data. I have Employee ID, Total Pay, and Hours.
2. I created a Custom Column to calculate Pay per Hour

= [Total Pay] / [Hours]

Note – Emp ID 6 returns an error because 200 is divided by a text = “nil”. To correct the above error, I am going use try and otherwise within the formula

= try [Total Pay]/[Hours] otherwise null

As an output, I got a null instead of an Error.

 

Power Query try and otherwise – Example 2

The try and otherwise statement can also be applied to the entire step.

1. I deleted the Custom Column for Pay/Hours
2. And Changed Type, results in an error since it wasn’t able to find the previous step

This is a step-level error that happened at the Changed Type Step.

To fix this error I will surround my entire formula with the try and otherwise statement in the formula bar.

= try
    Table.TransformColumnTypes(Source, {{'Pay/Hours', type number}})
otherwise
    #'Removed Columns'

If the above formula (which is trying to change the type of Pay/Hours) returns an error then the otherwise statement returns the previous step – Removed Columns. The query throws no errors.

 

Power Query try and otherwise – Example 3

This time let’s write the try statement (without otherwise). Let’s calculate Pay/Hours but this time with only the try keyword.

Create a new Custom column with the following formula,

= try [Total Pay]/[Hours]

As a result, it gives me a column that contains records.

Expanding Custom column is going to return 3 more columns.

1. HasError – Shows TRUE if the formula resulted in an Error.
2. Value – Is the output of the formula with no errors.
3. Error – Again contains records that will describe the error upon expanding.

Upon further expanding the Error column.

We again get 3 more columns,

1. Reason – This tells the reason for the error.
2. Message – This shows what actually the error is.
3. Detail – Tells the information of the error, message about the error.

On further expanding Detail column we get even more details about the error on that particular row.

So this is a very crude way of doing some very basic error reporting on your data in case your Power Query steps result in any errors.

 

 

More on Power Query

Promote Double Headers in Power Query

5 Tricks to Reduce Steps in Power Query

Remove Top Rows and Combine Data from Multiple Excel Files

 

Quick VLOOKUP in Power Query

Dynamic Unpivoting in Power Query