Power QueryExercises

Find, remove and replace errors

Handle cell errors deliberately instead of letting them reach reports.

~10 min read · beginner · Module 8: Profiling, errors and validation

By Tahir Nas · Examples run in our simulator · Published · Updated

Before this: null, empty text and errors are different things

Most errors appear when a conversion fails: typing "n/a" as a number, or "31/02/2026" as a date. Power Query keeps the error in that cell and carries on with the rest of the table. Only when something needs the value, such as a sum, a filter on that column or loading to Excel, does the error surface.

Three responses

GoalStepFunction
Drop bad rowsRemove ErrorsTable.RemoveRowsWithErrors
Keep the row with a neutral valueReplace ErrorsTable.ReplaceErrorValues
InvestigateKeep ErrorsTable.SelectRowsWithErrors

Here's the same typed data handled each way. Two of the five readings failed to convert:

let
    Source = #table({"Meter", "Reading"}, {{"M-1", "12"}, {"M-2", "n/a"}, {"M-3", "7"}, {"M-4", "--"}, {"M-5", "30"}}),
    Typed = Table.TransformColumnTypes(Source, {{"Reading", type number}}),
    Removed = Table.RemoveRowsWithErrors(Typed, {"Reading"})
in
    Removed

Result

MeterReading
M-112
M-37
M-530

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

let
    Source = #table({"Meter", "Reading"}, {{"M-1", "12"}, {"M-2", "n/a"}, {"M-3", "7"}, {"M-4", "--"}, {"M-5", "30"}}),
    Typed = Table.TransformColumnTypes(Source, {{"Reading", type number}}),
    Replaced = Table.ReplaceErrorValues(Typed, {{"Reading", null}})
in
    Replaced

Result

MeterReading
M-112
M-2null
M-37
M-4null
M-530

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

let
    Source = #table({"Meter", "Reading"}, {{"M-1", "12"}, {"M-2", "n/a"}, {"M-3", "7"}, {"M-4", "--"}, {"M-5", "30"}}),
    Typed = Table.TransformColumnTypes(Source, {{"Reading", type number}}),
    ForReview = Table.SelectRowsWithErrors(Typed, {"Reading"})
in
    Table.SelectColumns(ForReview, {"Meter"})

Result

Meter
M-2
M-4

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

A review query listing only the identifiers of bad rows is easy to send back to the data owner.

Choosing

Removing rows changes totals and counts, which is fine for junk lines and dangerous for real orders with a typo. Replacing with null keeps the row and makes the gap visible. Replacing with 0 hides the problem by inventing a measurement. A separate review query (Keep Errors) helps someone fix the source.

Whatever you choose, count it. A small check step such as Table.RowCount(Table.SelectRowsWithErrors(Typed, {"Reading"})) tells you how many rows were affected on each refresh. Two bad readings is noise; two hundred means the source changed and someone should know.

Where to find this in Excel

Excel for Windows
Home > Remove Rows > Remove Errors; Transform > Replace Values > Replace Errors; Home > Keep Rows > Keep Errors
What you see
Select the column first. Keep Errors is the quickest way to see which rows fail.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Dealing with errors in Power Query (Microsoft Learn).

Common mistakes

  • Removing error rows from real transactions and under-reporting totals.
  • Replacing errors with 0, which turns 'unknown' into a real-looking value.
  • Putting the Remove Errors step before the type change that creates the errors.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: right-click a column → Remove Errors / Replace Errors; Home → Keep Rows → Keep Errors. Click an Error cell to see the reason and message.
  • Excel shows a count of errors per query in the Queries & Connections pane after loading; click it to open a query listing them.
  • The simulator stores cell errors with the same reasons (DataFormat.Error, Expression.Error) and supports all three table functions.
Functions in this lesson:Table.RemoveRowsWithErrorsTable.ReplaceErrorValuesTable.SelectRowsWithErrors

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.

Practise: Remove rows whose quantity isn't a number (kite shop)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.