Error Handling

Overview

Error handling with ETL is done slighly differently.

In Excel, for example, you have an IFERROR function, which works like this:

= IFERROR(value, value_if_error)

In Power Query, the approach for error handling is a little different. Consider the following example data:

From the above, we would like to create a new query that multiplies the values from the [Price] and [Amount] columns to create a new Subtotal column.

In the event an error is found in the Price column, we would like to use the [List Price] instead of the [Price] value.

This article explains how to do this.

Steps

1. Load the table as a query

The data should look something like this:

2. Add a custom column

• Add a custom column, with the following try / otherwise statement

The result should look as follows:

3. Error Messages

If you need to know why the error is occurring:

• Remove the step called ‘Changed Type’
• Add another custom column, using a try statement

• Load the following fields

The table should look as follows:

• Then expand the ‘Error’ column, and remove the ‘HasError’ and ‘Value’ columns

The final table, with a message of what is causing the error, should be shown as follows:

Using HasError

As shown below, when the try expression succeeds, the result includes a record in which HasError is false, and the Value field contains the result of the successful expression:

The following shows how errors can be programmatically handled without the use of the otherwise keyword:

let
    Source = Excel.CurrentWorkbook(){[Name="Sample"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Record", each try[Price]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Price.1", each if [Record][HasError] then null 
else [Record][Value])
in
    #"Added Custom1"

The expression returns null if the Price does not exist, and is similar to using a try/otherwise expression:

You can also show a custom message, if there is an error, using the following code:

let
    Source = Excel.CurrentWorkbook(){[Name="Sample"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Record", each try[Price] otherwise error "Price is invalid")
in
    #"Added Custom"