How to Handle Errors in Power Query

Power Query is extremely powerful for cleaning and reshaping data, but it is also unforgiving: one bad value, missing column, or data type mismatch can cause refresh failures. Learning how to detect, understand, and handle errors is essential if you want your queries to be reliable and refresh automatically without breaking.

This guide walks through the main types of errors in Power Query and shows practical techniques to remove, keep, replace, or gracefully handle them using built‑in commands and the try … otherwise pattern in M.

1. Types of Errors in Power Query

Step‑level vs. cell‑level errors

• Cell‑level errors: Individual cells show Error in the preview grid (for example, dividing by zero, converting text to number when text is non‑numeric, or invalid dates). These errors affect specific rows but the query can still load if they aren’t referenced in later steps.
• Step‑level errors: A whole step fails (red ! icon) because something in that transformation cannot be evaluated (e.g., referencing a column that no longer exists, invalid function arguments, or data source connection failure).

Common causes

• Changing a column’s data type so some values cannot be converted (e.g., text to number).
• Dividing by zero, or other invalid mathematical operations.
• Referencing a column that was renamed or removed upstream (schema change).
• Data source errors: network issues, invalid credentials, or moved/renamed files.

2. How to Find Errors Quickly

Visual error indicators in the preview

In Power Query Editor, error cells are shown as the word Error in red. Clicking an error value opens a detailed dialog with the error message, error type, and sometimes a stack trace that points to the failing operation.

Use Data Profiling (Column Quality & Profile)

1. In Power Query Editor, go to the View tab.
2. Enable Column quality and optionally Column profile.
3. Each column now shows percentages of Valid, Error, and Empty values.
4. Click on the error percentage to quickly Keep Errors or Remove Errors for that column.

Data profiling is especially helpful when errors appear far down in large datasets and aren’t visible in the initial preview.

3. Remove, Keep, or Replace Errors (No Code)

Remove rows with errors

To completely remove rows that contain errors in specific columns:

1. Select the column(s) that contain errors.
2. Go to Home > Remove Rows > Remove Errors.
3. Power Query adds a Removed Errors step. Only rows without errors in those columns remain.

This is useful when error rows are not critical or are clearly bad records, but remember: you’re deleting data, which might affect totals and counts.

Keep only rows with errors (for auditing)

To investigate problem records, you can isolate all error rows:

1. Select the column with errors.
2. Go to Home > Keep Rows > Keep Errors.
3. The query now contains only rows where that column has an error.

You can duplicate your main query, apply Keep Errors in the copy, and use that as an error report while keeping the main query clean.

Replace errors with a value

To keep the rows but remove the error from a column:

1. Select the column with errors.
2. Go to Transform > Replace Values > Replace Errors, or right‑click the column header and choose Replace Errors.
3. Enter a replacement value (for example, 0, null, or "Unknown" depending on context).
4. Click OK.

This creates a Replaced Errors step that uses Table.ReplaceErrorValues under the hood.

4. Using try … otherwise (Power Query’s IFERROR)

Power Query does not have an IFERROR function like Excel, but the M language offers equivalent functionality through the try … otherwise construct. It lets you “try” an expression and, if it errors, return a safe fallback value instead.

Basic syntax

try <expression> otherwise <fallback_value>

• If <expression> succeeds, its value is returned and otherwise is ignored.
• If it fails, the value after otherwise is returned instead of stopping the query.

Example: safe division (avoid divide‑by‑zero)

= Table.AddColumn(
    #"Previous Step",
    "Safe Result",
    each try [Numerator] / [Denominator] otherwise 0,
    type number
)

Any row where the division would have caused an error (e.g., denominator is 0 or null) now gets 0 instead, and the query refreshes successfully.

Example: safe type conversion

Convert text to number without breaking on bad values:

= Table.AddColumn(
    #"Previous Step",
    "AmountAsNumber",
    each try Number.From([AmountText]) otherwise null,
    type number
)

Bad values become null, and you can later filter or replace them.

Using Table.ReplaceErrorValues for whole columns

The UI command Replace Errors produces code like this:

= Table.ReplaceErrorValues(
    #"Changed Type",
    {{"Value", 0}}
)

This is equivalent to wrapping [Value] in try [Value] otherwise 0, but is more concise when you just want to map all errors to a single default.

5. Building an Error Table for Debugging

For robust data pipelines, it’s often better to log errors than silently drop or overwrite them. A common pattern is:

1. Duplicate your main query.
2. In the duplicate, use Keep Rows > Keep Errors on suspect columns.
3. Optionally add columns describing the error context (source file, date, etc.).
4. Load this “errors only” query to a separate sheet or table as an error report.

This lets you keep production output clean while still investigating data quality issues upstream.

6. Handling Schema & Data Source Errors

Missing or renamed columns

If your source file or table changes (for example, a column is removed or renamed), step‑level errors occur when Power Query tries to reference a column that no longer exists.

You can protect against this with try … otherwise when accessing columns:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Safe Column" =
        Table.AddColumn(
            Source,
            "OptionalColumn",
            each try [NewColumn] otherwise null,
            type any
        )
in
    #"Added Safe Column"

If [NewColumn] doesn’t exist, the expression returns null instead of failing the entire query.

Data source connection errors

For web APIs or unstable data sources, wrap calls in try … otherwise so intermittent failures don’t break everything:

let
    SafeResponse = try Web.Contents("https://api.example.com/data") otherwise null,
    Parsed =
        if SafeResponse <> null
        then Json.Document(SafeResponse)
        else null
in
    Parsed

Downstream steps should then handle null responses (for example, filter them out or log them).

7. When to Remove vs. Replace vs. Fix at Source

• Fix at source when:
  • The same error occurs repeatedly (bad data entry, broken export, inconsistent CSV layout).
  • Errors indicate upstream process problems (e.g., missing mandatory fields).
• Replace errors when:
  • You need the row but can safely substitute a default (e.g., 0, null, or “Unknown”).
  • Errors would break downstream calculations but don’t change high‑level decisions.
• Remove errors when:
  • Error rows are clearly invalid or out of scope for your reporting.
  • Dropping them has negligible effect on analysis and is documented.

8. Best Practices to Avoid Errors

• Validate data types early: Let Power Query’s automatic “Changed Type” step run, then adjust types explicitly so you know where conversions happen.
• Use data profiling: Enable Column quality/profile to spot outliers, nulls, and incompatible values early.
• Break complex logic into steps: Instead of one huge custom column, add several simpler steps to make it obvious where an error appears.
• Apply filters early: Filter out obviously bad or irrelevant data before heavy transformations to reduce error surface and improve performance.
• Use dynamic references: Avoid hard‑coding file paths and column names; use parameters or pattern‑based file combinations so schema changes are less likely to break queries.
• Check folding impact: Apply try … otherwise as late as possible in queries that fold to databases, to avoid breaking query folding and hurting performance.

9. Quick Decision Guide

Goal	Recommended approach
Refresh must never fail due to bad values	Wrap risky expressions with try … otherwise and/or use Table.ReplaceErrorValues on key columns.
You need an error log for auditors	Duplicate query, use Keep Errors, and load that as a separate “error table”.
You want clean data for analysis, errors are unimportant	Use Remove Errors on affected columns and document the decision.
You must preserve rows but can use defaults	Use Replace Errors to replace errors with 0, null, or a meaningful placeholder.

The Bottom Line

Errors in Power Query are inevitable when working with real‑world data. The goal is not to eliminate them entirely, but to detect them early, understand their causes, and handle them deliberately. Use the built‑in commands (Remove/Keep/Replace Errors) for quick fixes, and adopt try … otherwise when you need robust, Excel‑style IFERROR logic embedded in your transformations.

By combining error logging, safe defaults, and good upstream data practices, you can build Power Query solutions that refresh reliably and give you confidence in the quality of your analytical output.