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
| Goal | Step | Function |
|---|---|---|
| Drop bad rows | Remove Errors | Table.RemoveRowsWithErrors |
| Keep the row with a neutral value | Replace Errors | Table.ReplaceErrorValues |
| Investigate | Keep Errors | Table.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
RemovedResult
| Meter | Reading |
|---|---|
| M-1 | 12 |
| M-3 | 7 |
| M-5 | 30 |
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
ReplacedResult
| Meter | Reading |
|---|---|
| M-1 | 12 |
| M-2 | null |
| M-3 | 7 |
| M-4 | null |
| M-5 | 30 |
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.
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.
Table.SelectRowsWithErrorsNext 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.
- beginner~7 minPro
Remove rows whose quantity isn't a number (kite shop)
Typing Qty as a whole number turns placeholders like n/a into errors. Remove those rows.
- beginner~7 minPro
Remove rows whose quantity isn't a number (bakery)
Typing Qty as a whole number turns placeholders like n/a into errors. Remove those rows.
- beginner~7 minPro
Remove rows whose quantity isn't a number (bike rental)
Typing Qty as a whole number turns placeholders like n/a into errors. Remove those rows.
- intermediate~8 minPro
Replace bad quantities with null and flag them (kite shop)
Keep every order, but turn unreadable quantities into null and add a Needs Review flag.
- intermediate~8 minPro
Replace bad quantities with null and flag them (bakery)
Keep every order, but turn unreadable quantities into null and add a Needs Review flag.
- intermediate~8 minPro
Replace bad quantities with null and flag them (bike rental)
Keep every order, but turn unreadable quantities into null and add a Needs Review flag.
- intermediate~7 minPro
List the rows whose amount can't be converted (travel)
Before cleaning, finance wants an audit list of claims whose Amount isn't a number, with the original text.
- intermediate~7 minPro
List the rows whose amount can't be converted (grants)
Before cleaning, finance wants an audit list of claims whose Amount isn't a number, with the original text.
- intermediate~8 minPro
Replace conversion errors with a different value per column (garden centre)
After typing, bad Discount values should become 0 and bad Price values null. One step can do both.
- intermediate~8 minPro
Replace conversion errors with a different value per column (bike parts)
After typing, bad Discount values should become 0 and bad Price values null. One step can do both.
- advanced~12 minPro
List every validation issue per lead (events)
Check three rules on each sales lead and report the failed ones in one Issues column, keeping only rows with problems.
- advanced~12 minPro
List every validation issue per lead (solar)
Check three rules on each sales lead and report the failed ones in one Issues column, keeping only rows with problems.