IFERROR in Power Query - try and otherwise

Summary

Power Query does not have an IFERROR function like Excel. Instead, it uses try and otherwise to handle errors. These can be applied to individual row calculations or to an entire query step.

Handle errors in a row calculation

Suppose you calculate hourly pay by dividing each employee’s total pay by total hours. A text entry such as nil in the hours column causes a division error.

Wrap the calculation in try and use otherwise to specify a fallback value, such as null or 0. Rows that would have produced errors then return the fallback value.

try [Total Pay] / [Total Hours] otherwise null

Handle errors in an entire query step

try ... otherwise can also catch an error from a step that produces a whole table, list, or value.

For example, a type-change step might refer to a column that was removed earlier. The step fails because the column cannot be found. Wrapping the step’s expression in try allows otherwise to return a different result, such as the preceding step, which still produces a valid table.

Use try without otherwise to inspect errors

When try is used without otherwise, Power Query returns a record describing the result instead of replacing an error. Expanding that record reveals:

• HasError — Whether the expression failed (true or false).
• Value — The result when successful; it is empty when the expression errors.
• Error — Details about the error, including its reason, message, and additional information.

This structure can be used to report data errors. For example, the error details can identify an attempt to divide a number by text. Refreshing after changing the source data updates which rows are flagged. A changed or missing column name can also cause errors to appear across the results.

Key takeaway: try ... otherwise is a flexible error-handling pattern in Power Query. Use otherwise to substitute a fallback result, or use try alone to inspect and report error information.

Speakers and sources

• Speaker: The presenter from the Goodly channel; no personal name is stated in the subtitles.
• Referenced source: Ben Gribaudo, author of an article about handling errors in Power Query.
• Referenced source: Microsoft documentation on handling errors in Power Query.