Power Query Keep Errors: Find and Inspect Error Rows

In Power Query for Excel on Windows, select the column or columns you want to check, then choose Home > Keep Rows > Keep Errors. Power Query filters the preview to rows containing an error in the selected columns; it helps you find and investigate errors, but it does not fix them.

What Keep Errors means

Keep Errors is a row-filtering command in the Power Query Editor. It retains rows where at least one selected cell contains a Power Query error value. If you select more than one column, a row is kept when an error appears in any of those columns. Other columns in each retained row remain visible, which is useful for identifying the source record and comparing its values.

If you do not select a particular column first, the command checks for errors across the table. To focus an investigation, select the relevant column header before running it. For example, selecting Amount and choosing Keep Errors returns rows whose Amount cell is an error; an error only in Notes will not qualify that row.

An error is not the same as a blank, null, or text that happens to say Error. A blank source value may become null, while the literal text Error remains ordinary text unless a later transformation interprets it. Keep Errors looks for actual error values created in the query, such as a failed conversion or an invalid operation.

How to keep error rows in Excel

Open the query in the Power Query Editor. In Excel for Microsoft 365 on Windows, select a cell in the query table and choose Data > Queries & Connections, then double-click the query. You can also edit a query from the Queries & Connections pane.
Find the column in which you suspect errors. Select its header. To check several columns, use Ctrl-click to select multiple headers.
On the Home tab, open Keep Rows and choose Keep Errors. The preview now shows only rows with errors in the selected column or columns.
Inspect the retained rows and error details. When you are done, decide whether to correct the cause, keep the filtered query for review, or remove the diagnostic step.

The exact ribbon availability can differ in Excel for Mac and Excel for the web because Power Query editing features and connectors vary by platform and build. The steps above describe Excel for Microsoft 365 on Windows. If your platform does not show Keep Errors, opening or editing the query in the Windows desktop editor is a practical option.

What Power Query changes behind the command

Power Query records each transformation as an Applied Step rather than overwriting the original source data. Keep Errors adds a filtering step to the query. In the M language, its underlying function is Table.SelectRowsWithErrors. A step might look like this:

Table.SelectRowsWithErrors(#"Changed Type", {"Amount"})

Here, Changed Type is the name of the preceding step and Amount is the column being checked. The names must match the query, including spaces and capitalization. If the optional column list is omitted, the function checks all columns:

Table.SelectRowsWithErrors(#"Changed Type")

These are Power Query M expressions, not worksheet formulas. You normally do not need to type them: the ribbon command creates the step for you. You can view or edit a step in the formula bar or Advanced Editor if you are comfortable with M.

Because steps run in order, Keep Errors only sees errors that exist at that point in the query. If you add a type-conversion step afterward, errors caused by that conversion will not be caught by a Keep Errors step placed before it. Put the diagnostic filter after the transformation that produces the errors you want to inspect.

How to inspect an error and find its cause

After filtering, click an Error value in the preview to see the available error information. Depending on the error, the details can include a message and information about the reason or underlying cause. Read that information alongside the values in the same row: an identifier, source text, date, or file name can help locate the original record.

For example, imagine a source table with ID and Amount columns. Amount contains the text 12.50 for one record, n/a for another, and an empty value for a third. If Changed Type converts Amount to a decimal number, n/a cannot be interpreted as a number and can become an error. Keeping errors in Amount isolates that record. The empty value may instead become null, so it will not necessarily appear in the error-only result.

Check which Applied Step first introduces the error. Selecting earlier steps can show whether the source already contained a problem or a later transformation created it.
Compare the failing value with successful values. Look for mixed formats, unexpected symbols, extra spaces, or values that do not match the expected type.
Check whether the issue affects one record, a whole column, or only files from a particular source. The pattern often points to a bad input value or an overly broad transformation.

Keep Errors is a diagnostic view, not a complete error report. It filters out non-error rows from the current preview, so it is not a good replacement for the full query when you need to compare error records with normal records. You can remove the filter step later or use a separate query for investigation.

Common causes of Power Query errors

Errors often arise when a transformation cannot be applied to a particular value. A Changed Type step is a frequent cause: text such as n/a, a date in an unexpected format, or a number containing an unrecognized decimal separator may not convert using the selected type and locale. A conversion can work for most rows and still fail on one unusual record.

Other causes include calculations that are invalid for particular inputs, references to a field that is missing from some records, or a function receiving a value of an unexpected type. Combining files can expose these differences: one file might have a column another file lacks, or the same field might contain numbers in one file and text in another.

Do not assume every visible warning or unusual value is an error. A null is a legitimate absence of a value, and a text value that looks wrong may still be valid text at that step. Use the cell’s error details and the Applied Steps list to establish whether Power Query has an actual error value and which transformation produced it.

Keep Errors versus Remove Errors

Keep Errors and Remove Errors are opposite filters, but neither repairs a bad value. Keep Errors retains the rows with errors in the chosen columns so you can inspect or report them. Remove Errors excludes those rows from the result at that point in the query. The corresponding M function for removing rows is Table.RemoveRowsWithErrors.

Use Keep Errors when you are auditing a refresh, locating records that failed a conversion, or creating a focused error-review query. Use Remove Errors only when dropping the affected rows is an intentional data-quality rule and the loss is acceptable. Removing rows can make a report look clean while silently omitting important records, so document the reason and consider counting or retaining the rejected rows elsewhere.

Another option is to keep the complete dataset and replace or handle errors rather than filter rows. That is useful when a row has valuable information in other columns even though one field is invalid. The right choice depends on whether the error means the whole record is unusable or only one value needs attention.

Ways to handle errors after finding them

Once you know the cause, choose a response that preserves the meaning of the data. If a value is simply formatted differently, correct the source or adjust the conversion and locale. If the value is invalid, you might replace the error with null, a suitable default, or a clearly labelled status, depending on the analysis. Avoid replacing every error with zero unless zero is genuinely the correct business meaning; a zero can be mistaken for a measured value.

Power Query also provides error-handling options through the interface, including replacing errors in a selected column. The resulting transformation is different from Keep Errors: it changes how error values are represented instead of filtering the row set. Review the proposed replacement carefully and verify the output after refresh, especially when a query combines changing files or data from multiple sources.

For a recurring problem, it is usually better to make the query robust at the step that causes the error than to rely on manually inspecting a filtered preview each time. That might mean setting the correct data type, cleaning input text before conversion, or explicitly handling exceptional values. Keep a separate diagnostic query or step when you still need visibility into records that fail the rule.

Common problems and useful checks

No rows appear: Check whether the selected column actually contains error values at the current step. Errors might be in another column, or they may be created by a later step. Try selecting the relevant column or checking all columns.
A row with a blank is missing: A blank or null is not automatically an error. If blanks are invalid for your task, filter for nulls separately or add a validation rule.
The error seems to disappear after refresh: The source data or an earlier transformation may have changed. Refresh updates the query from its source, so the error-only result can change as inputs change.
Only some files fail: Compare their column names, types, and value formats. A combined-files query may encounter a schema or formatting difference that was not present in the sample file.
You cannot find the original record: Keep identifiers and source information in the query while diagnosing. Removing identifying columns too early makes it harder to trace an error back to its source.

Remember that the filtered preview is one stage in a sequence of transformations. If you later remove or move the Keep Errors step, the set of displayed rows can change. Review the Applied Steps order before concluding that a source record has been deleted or repaired.

Frequently Asked Questions

Does Power Query Keep Errors fix the errors?
No. It retains rows that contain errors so you can inspect them. To fix the issue, change the source value or transformation, replace or handle the error, or remove the affected rows if that is appropriate.

Can I keep errors from just one column?
Yes. Select that column’s header before choosing Home > Keep Rows > Keep Errors. The filter then keeps rows with errors in that column rather than using errors elsewhere in the table to qualify a row.

Why does Keep Errors return no rows?
The selected columns may have no error values at the step where the command runs. Check the step order, select the column that actually contains the error, or check the whole table. A blank or the text Error does not count as an error value by itself.

Will Keep Errors change my source workbook?
No. It adds a transformation to the query; it does not edit the source file or cell values. When you load or refresh the query, the output reflects the transformations and the current source data.

Can I use Keep Errors to review errors before removing them?
Yes. Keep Errors can isolate problematic rows for investigation. You can then adjust the transformation or remove the diagnostic filter, and choose separately whether any rows should be excluded from the final output.

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.