I also recommend that you check out this post on Query Error Auditing so you get a better understanding of what types of errors you can find in Power BI / Power Query.
This is a post on how to use error handling, similar to an IFERROR in DAX and Excel, but for Power Query (using its M language).
How does Error handling works in Excel & DAX?
In Excel and in DAX we have the IFERROR function which works like this:
=IFERROR( value, value_if_error)
Taken directly from the official DAX documentation:
Evaluates an expression and returns a specified value if the expression returns an error; otherwise returns the value of the expression itself.
It’s a pretty simple and straightforward function in DAX and Excel, where you can enter your formula in the “value” parameter and then, if you get an error from it, you can define what should be the output value in the “value_if_error” parameter.
The whole idea is that you can “catch” an error and use a different value when it finds an error.
How does Error handling works in Power BI / Power Query?
In Power Query the code is a bit different. Let’s see it in action and then talk more about it.
Imagine that we have an Excel workbook with a table like this
What we would like to create is a new column that should multiply the values from the [Price] and [Amount] columns to create a new Subtotal column.
One caveat, as you can probably see, is that this spreadsheet has some cells with errors on the [Price] column. In the event that we find an error on the Price column, we need to use the value from the [List Price] instead of the [Price] value.
The first thing that we need to do is import that table from Excel. If you’d like to follow along, you can download the workbook by clicking the button below:
Importing data from the Excel Workbook
I’ll be using Power BI Desktop for this, but you can use Excel as well.
The first thing that we need to do is select the Excel connector and connect to our file:
and once you get the “Navigator” window, you can select the table that reads “Sample”:
Notice how there’s a bunch of errors in that [Price] column just in the preview. Let’s hit the “Edit” button so we can go to the Power Query Editor Window.
Using error handling in Power BI /Power Query
Now that we have our data in the Power Query Editor window:
In there, we try do create a simple column that will multiply the [Price] column by the [Amount] column:
and as you can see, our [Subtotal] column has some errors.
We know that in Excel and DAX you can use IFERROR, but what can you use in Power Query ?
For Power Query, we need to hit modify that Custom Column code (just click the gear icon next to the Added Custom step) and add the following pieces to it:
try [Price]*[Amount] otherwise [Amount]*[List Price]
We need to use the keywords “try” and “otherwise”. It’s pretty easy to read, but it just says to try and evaluate the expression ([Price] * [Amount]) and if that gives an error, use the expression defined after the otherwise statement.
The result of that will look like this:
pretty simple! almost as simple as the IFERROR function in DAX and Excel where intellisense does explain you a bit how to use that function, but in Power Query you need to understand how this works in order to use it. Is nowhere in the User Interface of Power Query, so you need to write this code manually.
The workbook sample that I’m using is fairly simple. I’ve had experiences where some users / customers absolutely need to know when a specific error is found from an Excel Workbook.
What happens with Power Query is that it just flags any errors found as “Error” but, what if you needed to know WHY it shows as an error?
Let’s go back to our initial load of the file. Remember that in most cases Power Query will automatically try to add a “Changed Type” step, so what if we remove that step?
Well, I removed the step and I’m still seeing the errors and that’s because the error wasn’t triggered by a data type conversion, but rather it’s a source error, meaning that the error comes directly from the Excel Workbook.
In Workbook with vast amounts of rows, it’s hard to tell if there are any errors at all and doing a “Replace Errors” will not tell us why those errors occurred. We NEED to know what is the error from the source because we want to handle each type of error differently.
Error Message and Error Reason
To figure out what’s the reason why there’s an error, we need to use the “try” statement again.
Note how I only use “try” and not the “otherwise” statement. This will give me a new column with record values. We can expand those records like this:
the most important field from those records it’s the “Error” field which can be either a null or a record value:
and after expanding that column and deleting some others that we don’t need, I end up with this:
I’ve highlighted the most important field after this whole process which is the “Message” which tells me exactly the reason why this is an error.
I can later use this to my advantage and target specific errors differently or get a report of ALL the errors found on a series of files that my department / group uses. This is extremely helpful if you’re trying to validate everything and make sure that we don’t have any errors at the source.
Don’t forget that these same principles work for both Step and cell Value level errors.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.