Error handling in Power Query

Power Query is a very powerful tool. It has many inbuilt functions that helps you in data transformation and data preparation. Error Handling allows you to control error while loading, transforming or preparing the data in Power Query.

It is always a best practice to clean the data before using it. There should not be any error also calculations should be capable of handling an error it self.

Let’s see how to handle error in Power Query. Assume that, you have loaded the data into Power BI and you get an error for few records.

You can see, there are few rows in Qty column having an error.

Let’s see the error, just click on cell and you can see the error.

And you can see the error is DataFormat Error. As data type of Qty column is Integer while there are some values which are non integer.

Let’s see the various functions available in Power Query to handle an error.

Replace Errors

This features allows you to replace the error with other values.

Click on Qty column Header then select Replace Errors..

You can see, a pop up window Replace Errors appears.

Now you can replace the error with zero, after that click on OK button.

Now you can see, error values has been replaced with zero.

Remove Errors

Remove Errors function to remove rows those are having an error.

Use this approach only when you want to clean up the data and do not want to fix an error because it removes rows from table.

Click on Qty column Header, then select Remove Errors.

You can see, it removes all rows those are having an error.

Error handling using try otherwise

Now we will see, how to handle an error in formula. Let’s not clean the data and keeps an error as usual in QTY column.

Note that, here we are not handling an error in QTY column deliberately in order to produce an error in formula which will reference to QTY column.

The custom column that we are going to create return the Cost Per Qty that is dividing Cost by Qty.

So, the idea is first we will produce an error in formula then we handle that error.

Lets add a column (Custom Column) in table as shown below.

Cost Per Qty = [Cost]/[Qty]

Once you create custom column, you can see there are few rows having an error.

Let’s handle the error in Power Query using try otherwise function. It allows us to provide an alternative value if find an errors.

Now, modify the above formula as given below.

Cost Per Qty = try [Cost]/[Qty] otherwise 0

Now you can see, errors are handled and replaced with an alternate value that is 0.

Capture the reason of an error

In case, if you want to see the detailed information about an error that is also possible in Power Query.

Let’s modify above formula, you just need to remove otherwise keyword as shown below.

Once you modify the formula, you can see it gives you record as shown below.

Now expand the column, and you will get three more columns that is HasError, Value and Error as shown below.

Just click on OK button and these columns are add to your table.

HasError tells you whether the value from the Cost Per Qty column has an error or not. It returns True , if value has an error other wise False.

Value if the value from the Cost Per Qty column has no error, then this will display the value from the Cost Per Qty column otherwise it displays null.

Error if the value from the Cost Per Qty column has an error, then this will display the error record for the value from the Cost Per Qty column otherwise for values with no errors, this will not be available and will show null during the expand operation.

Lets expand the Error column to see the reason of an error.