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.
Once you expand the column, you will see five columns. Reason, Message, Detail, Message.Format, Message.Parameters.
Just select Reason and Message column then click on Ok button to add them in table.
Reason tells you the reason of an error. You can the reason of an error is DataFormat.Error.
Message tells you the exact error message, you can see the message “We couldn’t convert to Number”.
Once you add the columns, you can see it gives you the detailed reason of an error.
Also Read..
Adding Prefix and Suffix to values in column
Handle NULL values in Power Query
Add comments to Power Query steps
Create and invoke custom function
Create Custom Function to get two dates difference