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"
Feedback
Submit and view feedback