How to Handle Errors in Power Query in Power BI

In Power Query for Power BI, you handle errors using functions like try ... otherwise to catch errors and provide fallback values. You can also use Table.RemoveRowsWithErrors to clean error rows or Table.ReplaceErrorValues to replace errors with default values.

Why This Happens

Errors in Power Query often happen when data contains unexpected values, like text in a number column or missing data. When Power Query tries to perform operations on such data, it throws errors that stop the query from loading.

powerquery

let
    Source = Table.FromRecords({[Value="Text"], [Value=10]}),
    Added = Table.AddColumn(Source, "Double", each [Value] * 2)
in
    Added

Output

Expression.Error: We cannot apply operator * to types Text and Number

The Fix

Use try ... otherwise to catch errors and provide a safe fallback value. This prevents the query from breaking and lets you handle errors gracefully.

powerquery

let
    Source = Table.FromRecords({[Value="Text"], [Value=10]}),
    Added = Table.AddColumn(Source, "Double", each try [Value] * 2 otherwise null)
in
    Added

Output

[
  {Value="Text", Double=null},
  {Value=10, Double=20}
]

Prevention

To avoid errors, always check and clean your data before transformations. Use Table.RemoveRowsWithErrors to drop error rows or Table.ReplaceErrorValues to replace errors with default values. Validate data types early and use try ... otherwise for risky calculations.

Related Errors

Common related errors include type mismatch errors and null reference errors. For type mismatches, use Value.Is to check types before operations. For nulls, use if ... then ... else to handle missing values.

Key Takeaways

Use try ... otherwise to catch and handle errors in Power Query expressions.

Clean data early by removing or replacing error rows to prevent query failures.

Validate data types before calculations to avoid type mismatch errors.

Use Table.RemoveRowsWithErrors and Table.ReplaceErrorValues for bulk error handling.

Always provide fallback values to keep your queries running smoothly.