Power Query Error Trapping in Excel Explained

Errors & fixes · Updated November 19, 2026

In Excel Power Query, trap a row-level error with M’s try expression, then decide whether to replace it, keep its details, or exclude the row. For example, in a Custom Column, try Number.From([Amount]) otherwise null attempts to convert the Amount value and returns null if that conversion raises an error.

Error trapping is useful when a refresh encounters occasional bad values, but it should not hide problems that need fixing. The right approach depends on whether you want a clean output, an audit trail, or a query that stops when data is unreliable.

What Error Trapping Means in Power Query

Power Query is Excel’s data import and transformation tool. Its steps are written in the M language, and a step can produce an error when a value cannot be converted, a field is missing, or an operation cannot be completed. A common example is converting a column to a number when one row contains text such as “pending.”

Error trapping means handling an error at the point where it occurs rather than letting it stop the whole transformation. In M, try attempts to evaluate an expression. You can follow it with otherwise and provide a fallback, or inspect the error record to make a more informed choice. Trapping an error does not make the underlying value valid; it gives you control over what the query does next.

This is different from fixing a source problem. If a workbook has a wrong column name, a file is unavailable, or credentials are invalid, replacing a bad cell value will not necessarily repair the query. First identify whether the failure is in individual data or in the connection, structure, or query itself.

Why Errors Occur During a Refresh

Errors often appear when incoming data does not match the assumptions in a query. For instance, a column expected to contain dates may include a blank marker, a misspelled month, or a date written in a format that Power Query cannot interpret under the selected locale. A Changed Type step can reveal these mismatches.

Other common causes include:

  • A number conversion encounters text, an invalid symbol, or a value outside the expected format.
  • A calculation divides by zero or refers to a value that is not available.
  • A later step expects a column that has been renamed or removed from the source.
  • A nested value, such as a record or list, does not have the field the query expects.
  • A file or data source changes its layout between refreshes.

Not every failed refresh is a row-level error. A permissions problem, unavailable network location, or broken source path usually needs attention at the connection or source level. Wrapping a cell conversion in try is not a general-purpose fix for those issues.

Use Try Otherwise for a Simple Fallback

For a straightforward case, put try before the operation and otherwise before the fallback. In the following expression, Power Query tries to convert the current row’s Amount value to a number and returns null if the conversion fails:

try Number.From([Amount]) otherwise null

To add this expression in Excel for Microsoft 365 on Windows, open the query from Data > Queries & Connections, then in Power Query Editor choose Add Column > Custom Column. Give the new column a useful name, such as Amount as Number, and enter the expression. Confirm the result before removing or replacing the original column.

Suppose Amount contains the values 125, pending, and 80. The expression returns 125, null, and 80 respectively. The invalid word is no longer an error in the new column, but the query has not established what the word means. Null is appropriate when the value is genuinely unknown or unusable; it is not automatically a good substitute for every error.

Other possible fallbacks include a known default or a separate label, but choose them carefully. Replacing an error with zero can make totals look complete even though the source contained a bad value. A fallback should reflect the meaning of the data, not merely make the preview look tidy.

Keep Error Details Instead of Hiding Them

When you need to investigate failures, use try without immediately discarding its result. It returns a record indicating whether an error occurred. On success, the record has a Value field; on failure, it has an Error field containing details such as a message.

For example, this Custom Column expression returns a record with a usable numeric value when conversion succeeds, or a null value and an error message when it fails:

let
result = try Number.From([Amount])
in
if result[HasError] then
[Value = null, ErrorMessage = result[Error][Message]]
else
[Value = result[Value], ErrorMessage = null]

After adding the column, use its expand control to expand the record into separate Value and ErrorMessage columns. You can then filter for non-null messages, review the affected source rows, or retain the messages in a quality-check output. This is more useful than replacing every error silently when the data needs correction.

For a durable process, include enough context to locate each bad record. Keep an identifying field such as an order number, source file, or row key alongside the error message. A message without a way to find the original record may be difficult to act on.

Use the Editor to Find, Remove, or Replace Errors

You do not always need to write M to inspect errors. In Power Query Editor, select a column and use Home > Keep Rows > Keep Errors to display rows with errors in that column. This is a useful diagnostic step: it narrows the preview to the records that need attention. It does not, by itself, correct them.

To remove error rows, use Home > Remove Rows > Remove Errors. To replace errors in a selected column, use Transform > Replace Errors and specify the replacement value. These commands affect the query step and should be used only when dropping or replacing the affected data is acceptable. Check which column is selected, because removing errors from one column is not the same as removing every row with an error anywhere in the table.

A sensible investigation sequence is:

  1. Select the column that displays errors and keep the error rows so you can inspect them.
  2. Read the error details and compare the failing values with nearby valid records.
  3. Decide whether the source needs correction, whether a controlled conversion is appropriate, or whether the row should be excluded.
  4. Apply the chosen transformation and check the preview, data types, and row count.
  5. Refresh the query again to confirm the handling works with the current source data.

On Mac, Power Query availability and editor features can differ from Excel for Windows, and some capabilities may depend on the Excel release. Excel for the web also does not provide the same full Power Query authoring experience as the Windows desktop editor. If a command is absent, check the capabilities of your specific Excel version rather than assuming the query logic itself has changed.

Choose a Policy That Fits the Data

Error handling is a data-quality decision. Consider how the output will be used, whether errors are rare or widespread, and whether a fallback could change a result that someone relies on. A cleaned table for informal exploration may tolerate nulls; a report used for operational decisions may need a visible error log and a refresh check.

  • Return null: Useful when an invalid value should remain blank and downstream calculations can handle missing values.
  • Use a default: Appropriate only when the default has a defensible meaning. Do not substitute zero for unknown amounts unless zero is genuinely correct.
  • Keep an error record: Useful when someone needs to correct source data or monitor recurring quality problems.
  • Remove the affected row: Suitable only when losing that record is acceptable and the exclusion is documented.
  • Let the query fail: Often the safest choice when an error signals that a required value is missing or a transformation would otherwise produce misleading output.

Also decide whether errors should be handled for every row or only for a known, specific condition. Broad handling can conceal a new issue introduced by a source change. A narrow rule plus an error report is often easier to maintain than a blanket replacement.

Common Mistakes and Troubleshooting

A frequent mistake is treating null and error as the same thing. Null represents an absent value; an error means Power Query could not complete an operation. If an upstream step already converted a problem into null, later steps cannot recover the original error message. Add diagnostic handling near the operation that can fail.

Another mistake is applying try too late. If an earlier step fails while converting a column, a later step that refers to the failed result may not provide the row-level handling you intended. Inspect Applied Steps and put the error handling around the risky operation itself, such as the conversion, field access, or calculation.

When a query still fails after adding a fallback, check the exact error location and scope. The error may occur in another column, in a different step, or while Power Query is accessing the source. Confirm that the column name and data type are correct, and test the query on error rows as well as normal rows. A successful preview on a few displayed records does not prove that every source record is valid.

Finally, avoid assuming that removing errors is harmless. It changes the rows delivered by the query and can affect totals, counts, and joins. If you choose removal, compare row counts before and after and record why those records were excluded.

Frequently Asked Questions

How do I catch an error in an Excel Power Query custom column?
Use a try expression around the operation that may fail, followed by otherwise and a deliberate fallback. For example, try Number.From([Amount]) otherwise null catches a failed number conversion for the current row.

Can Power Query replace errors with blank values?
Yes. In M, use try expression otherwise null for a specific operation, or use Transform > Replace Errors in Power Query Editor for a selected column. Verify that a blank is a truthful representation of the failed value.

How can I see which rows contain errors?
Select the relevant column in Power Query Editor and choose Home > Keep Rows > Keep Errors. To retain a message in the query result, inspect the record returned by try and keep its Error details alongside a row identifier.

Does try otherwise fix a failed data connection?
Not usually. It is mainly for handling errors raised while evaluating the expression it wraps. A missing file, invalid credentials, or unavailable source generally requires fixing the connection, access, or source path.

Should I replace Power Query errors with zero?
Only if zero is the correct meaning for those records. If the value is unknown or invalid, zero can distort sums and averages. Null, an explicit error log, or a corrected source may be more appropriate.

A Reliable Way to Handle Errors

Use Power Query’s try construct to control errors, but pair it with a clear policy. For routine conversion problems, a carefully chosen fallback can keep a refresh running. For data that needs correction, preserve the error message and enough row context to find the source. For critical transformations, allowing the query to fail may be safer than producing plausible but incorrect results. After any change, inspect the affected rows and verify the refreshed output, not just the formula or preview.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.