Power Query Error Handling in Excel: 5 Practical Fixes

Errors & fixes · Updated November 19, 2026

In Excel Power Query, inspect the error to find its cause, then choose Transform > Replace Errors to substitute a suitable value or Home > Remove Rows > Remove Errors to discard affected rows. For a more controlled fix, use an M expression such as try Number.From([Amount]) otherwise null in a custom column; this attempts the conversion and returns null if it fails.

What a Power Query error means

Power Query is Excel’s data import and transformation tool. It reads data from sources such as workbooks, text files, databases, and web services, applies recorded transformation steps, and loads the resulting table to a worksheet or the Data Model. An error means a value could not be evaluated at some point in that process. It is different from a blank cell: a blank is a value Power Query can handle, while an error represents a failed operation.

Errors often appear after a step changes a column’s type or performs an operation that assumes clean input. A cell containing the text pending cannot be converted to a number, for example. A date operation may fail when a source contains an unrecognized date, and a calculation can fail when a referenced value is already an error. One error in a column does not necessarily mean the entire query is unusable, but it can affect later steps or prevent a successful load.

The key decision is not simply whether to make the red error indicators disappear. Decide whether the affected record is invalid, whether a sensible substitute exists, or whether you need the original error details to repair the source. The right choice depends on what the data represents.

Inspect the error before changing it

In Excel for Microsoft 365 on Windows, open the query from Data > Queries & Connections, then double-click it or choose Edit to open Power Query Editor. Select an error cell to see the message and, where available, details about the failure. The message can distinguish a conversion problem from a missing field, a failed lookup, or an issue in an earlier step.

Use the applied steps list to identify where the error first appears. In Query Settings, click steps in order, starting with the earliest transformation that produces an error. If the source preview is clean but a later step is not, the issue likely comes from that transformation or from an assumption it makes. For example, a type-change step may be the first place where text such as unknown becomes an error.

Column quality can help you locate problem fields. In Power Query Editor, open the View tab and enable Column quality. The indicators summarize valid, error, and empty values in the preview. The preview is not always a complete audit of a large data source, so use it to guide investigation rather than as proof that every row has been checked.

Five practical ways to handle errors

Choose the action that preserves the meaning of the data. These are five common approaches:

  1. Correct the source value. If an amount is meant to be numeric but contains a typo, fixing the source is often the most accurate solution. Refresh the query after correcting it.
  2. Replace errors with a known value. Select the relevant column and choose Transform > Replace Errors. Enter a replacement that matches the column’s meaning and type. Replacing an invalid number with zero is only appropriate if zero genuinely represents the intended value.
  3. Remove rows with errors. Choose Home > Remove Rows > Remove Errors to exclude rows containing errors in the selected column. This is useful when those records are unusable, but it can silently reduce totals or counts if you do not review what was removed.
  4. Keep error rows for investigation. Choose Home > Keep Rows > Keep Errors to isolate records that need attention. This is useful for creating a review query while leaving the main query’s data intact.
  5. Use a guarded transformation. In a custom column, wrap a conversion in M’s try ... otherwise pattern. For example, try Number.From([Amount]) otherwise null returns a number when conversion succeeds and null when it fails.

For the custom-column example, select Add Column > Custom Column, enter the expression in the formula box, and give the new column a meaningful name such as Amount_Clean. M expressions in that dialog do not start with an Excel worksheet equals sign. Check the new column’s type and compare its results with the original before removing or replacing anything.

When try and otherwise are useful

Power Query uses the M language, and try lets an expression run without immediately stopping the query when that expression fails. Adding otherwise specifies a fallback result. In try Number.From([Amount]) otherwise null, the conversion is attempted for each row; a value that cannot be converted becomes null instead of an error.

This is useful when the fallback is defensible and the refresh must continue. It is not automatically a safe cleanup method. If a failed amount becomes null, later totals may omit it, and users may not realize any values were rejected. If the query needs to distinguish a legitimate blank from a conversion failure, preserve a separate status or error-detail column rather than collapsing both cases into null.

A plain try without otherwise returns a record describing the result, including whether an error occurred and the value or error information. That can support a diagnostic workflow: add a column using try Number.From([Amount]), inspect or expand the resulting record, and retain the failure information for review. The exact fields to expand depend on the result and on what you need to report. Avoid exposing technical error messages as business-facing descriptions without explaining what they mean.

Common causes and targeted fixes

Many query errors come from ordinary inconsistencies in source data or from transformations applied too early. Identify the cause before deciding on a replacement:

  • Mixed types in one column: a column may contain numbers alongside labels such as n/a. Filter or classify those labels before converting the remaining values, or handle them explicitly.
  • Locale-specific numbers and dates: values such as 1,25 or dates written day-first may be read differently under another regional setting. Use the column’s data type options to convert using an appropriate locale when the source format requires it.
  • Missing or renamed columns: a later step may refer to a column that a source file no longer provides. Check the step that first fails and confirm whether the source structure changed; do not just delete a step if later calculations depend on that field.
  • Unexpected nulls: a transformation may assume a value is present. Decide whether null should remain null, be replaced with a documented default, or be excluded for that operation.
  • Arithmetic or lookup assumptions: a calculation may use an invalid input, or a merge and subsequent expansion may not produce the fields expected. Inspect the input columns and the steps immediately before the error.

A frequent misconception is that every error should become zero. That can distort averages, totals, and business decisions: zero is a real numeric value, not a neutral synonym for unknown. Likewise, removing every error row can make a report look clean while hiding incomplete source data. Treat a replacement or removal as a data rule, and document the rule where users of the workbook can understand it.

Make the fix stable across refreshes

A query is a sequence of steps, not a one-time repair of the current preview. When the source changes, Excel reruns those steps during refresh. A manual edit to a loaded worksheet table will generally not repair the underlying query transformation; the next refresh can overwrite that edit. Put recurring cleanup logic in Power Query or correct the upstream source instead.

After changing a query, check more than whether the error icon disappears. Verify the row count, important totals, column types, and a sample of records that previously failed. If you remove errors, compare the number of removed records with a known source count. If you replace errors, confirm that the replacement is appropriate and that downstream calculations treat it as intended.

Then refresh the query using Data > Refresh All and review the loaded result. A fix that works only on the current preview may fail when new rows arrive, particularly if the new data contains a different spelling, format, or column structure. Keep an error-review query or a rejected-record count when the cost of silently losing data is high. In Excel for Mac, Power Query availability and interface details can vary by Excel release; use the query editing and refresh controls provided in that version rather than assuming every Windows ribbon option is present. Excel for the web has more limited query editing capabilities than the Windows desktop application.

How to choose the right approach

Use replacement when there is a clear, documented fallback, such as a defined category for an unrecognized text value. Use removal when the row is genuinely invalid for the task and you can account for the excluded records. Use a guarded conversion when a small number of malformed values should not stop the whole refresh, but preserve a way to identify what failed. Correct the source when the value is a mistake and the source can be maintained reliably.

For recurring reports, separate clean output from diagnostic information where practical. A main query can contain usable rows, while a second query or a status column identifies records requiring correction. This makes the result easier to use without implying that the source was perfect. In a one-off exploratory query, filtering errors may be adequate, but note the decision if others will rely on the workbook.

There is no universal error-handling setting that knows what a business value should mean. Power Query can apply the rule consistently, but the person designing the query must decide whether a bad value is missing, invalid, safely replaceable, or important evidence that needs review.

Frequently Asked Questions

How do I replace errors with blanks in Power Query?
Select the column in Power Query Editor and choose Transform > Replace Errors, then enter null as the replacement if a blank-like value is appropriate. Alternatively, use try Number.From([Amount]) otherwise null for a numeric conversion. Remember that null can hide the fact that a value failed; keep a diagnostic indicator if that distinction matters.

Can I remove all error rows from a query?
You can select the relevant column and choose Home > Remove Rows > Remove Errors. This removes rows with errors in that column, so review the row count and consider whether errors in other columns also need handling. Removing records is a data decision, not merely a display cleanup.

Why does an error return after I refresh?
Refresh reruns the query steps against current source data. A new row, changed column name, or newly formatted value can trigger the same failing transformation again. Fix the transformation or source rule that causes the error, then test by refreshing rather than editing only the loaded worksheet cells.

Is null the same as an error in Power Query?
No. Null represents an absent value, while an error indicates that an operation failed. Replacing errors with null can be useful, but after that replacement the result no longer distinguishes an original blank from a failed conversion unless you preserve additional diagnostic information.

Should I replace Power Query errors with zero?
Only when zero is the correct business meaning for those records. If the value is unknown or invalid, zero can change totals and averages and mislead readers. Consider null, a clearly labeled status, correcting the source, or isolating the affected rows instead.

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.