Power query обработка ошибок

In Excel we can use IFERROR to if our calculation results in an error, and we can then tell Excel to produce a different result, instead of the error.

Power Query doesn’t have IFERROR but it does have a way of checking for errors and replacing that error with a default answer, it’s called try otherwise

In this post I’ll show you how to use try otherwise to handle errors when loading data, how to handle errors in your transformations and how to handle errors when your query can’t locate a data source.

Watch the Video

Download Sample Excel Workbook

First up, let’s load data from this table.

I’ve already generated a couple of errors in this table, and of course I can obviously see them and I could fix them before loading into Power Query.

But when using Power Query this isn’t always the situation. Your query will be loading data without knowing what it is so how would it handle these errors?

Let’s load the data into Power Query and call it Errors from Sheet

Straight away you can see the errors in the column.

Now of course you could use Remove Errors but that would remove the rows with the errors and that’s not what I want.

Or I could use Replace Errors, but this doesn’t give me any idea what the cause of the error is.

I want to see what caused the error and to do this I’ll add a Custom Column and use try [End]

This creates a new column with a Record in each row

In this record are two fields. HasError states whether or not there’s an error in the [End] column

If there is an Error then the 2nd field is another record containing information about that error

If there isn’t an error, then the 2nd field is the value from the [End] column

If I expand the new column I get 3 new columns containing the HasError value which is boolean, and either an Error or a Value

Checking what’s in the Error Records, you can see the Reason for the error, DataFormat.Error, this is from Power Query

There’s the Message, which is the error from the Excel sheet, and some errors give extra Detail, but not in this case.

If I expand this Error column I can see all of these fields.

I’ve ended up with a lot of extra columns here and it’s a bit messy so let’s tidy it up. In fact I’ll duplicate the query and show you another way to get the same information in a neater way

The new query is called Errors from Sheet (Compact) and I’ve deleted all steps except the first two.

What I want to do is , check for an error in the Try_End column, and if there is one I want to see the error message from Excel.

If there isn’t an error I want the value from the [End] column.

I can do all of this in a new column using an if then else

Add a new Custom Column called Error or Value and enter this code

What this is saying is:

• If the boolean value [HasError] in the [Try_End] column is true then
• return the [Message] in the [Error] record of the [Try_End] column
• else return the [Value] from the [Try_End] column

With that written I can remove both the End and Try_End columns so the final table looks like this

Checking for Errors and Replacing Them With Default Values

In this scenario I don’t care what the error is or what caused it, I just want to make sure my calculations don’t fail.

I duplicate the original query again, calling this one Error in Calculation, and remove every step except the Source step

I add a new Custom column called Result and what I’ll do here is divide [Start] by [End]

this gives me an error as I know it will in rows 1 and 3

so to avoid this, edit the step and use try .. otherwise

now the errors are replaced with 0.

Errors Loading Data from A Data Source

I’ll create a new query and load from an Excel workbook

Navigating to the file I want I load it

and loading this table

I’m not going to do any transformations because I just want to show you how to deal with errors finding this source file.

I’ll open the Advanced Editor (Home -> Advanced Editor) and change the path, so that I know I’ll get an error. Here I change the drive letter to X.

I don’t have an X: drive so I know this will cause the workbook loading to fail.

So that’s what happens when the file can’t be found so let’s say I have a backup or alternate file that I want to load if my main file can’t be found.

Open the Advanced Editor again and then use try otherwise to specify the backup file’s location

close the editor and now my backup file is loaded.

IMPORTANT: You can read the official documentation that Microsoft has on this topic from the following link (url).

If you haven’t read the first two posts (Part 1 | Part 2) in this series yet, I welcome you to do so before reading this one.

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:

what we want to do is create a Custom Column, so we simply go to the “Add Column” menu and hit on “Custom Column”.

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.

Understanding Errors

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.

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