Remove errors in Power Query

Real source data is dirty. A column that should be numeric arrives with "N/A", a trailing note, a stray dash. When Power Query changes that column's type to number, those cells become error values. They aren't zeros and they aren't blanks. Each one is a special "this cell is an error" value, and it propagates. Run List.Sum over a column with one error cell and you get an error back, so a single bad cell poisons your total. M's answer is the try expression.

try <expression> never errors itself. It catches. It evaluates its expression and returns a record describing what happened:

• on success: [HasError = false, Value = <the value>]
• on failure: [HasError = true, Error = <an error record with Reason, Message, Detail>]

So try [Amount] on a good cell yields [HasError=false, Value=1200.5], and on a bad cell it yields [HasError=true, Error=…]. You can branch on the HasError field or log the Error message. Most often, though, you'll supply a fallback with otherwise:

try [Amount] otherwise 0

Read it as "try to give me [Amount], and if that errors, give me 0 instead." Wrap it in an added column and you've neutralised a dirty numeric column inline:

Table.AddColumn(Typed, "CleanAmount", each try [Amount] otherwise 0, type number)

Now every error cell contributes 0 and the sum survives.

How do you handle error rows in Power Query?

The ribbon covers the common cases too. Select the offending column and use Home ▸ Remove Rows ▸ Remove Errors. That generates Table.RemoveRowsWithErrors, which drops the rows whose value in that column errored. Or right-click the column header ▸ Keep Errors, which generates Table.SelectRowsWithErrors and keeps only the error rows. To replace errors with a value, use Transform ▸ Replace Errors.

You have three legitimate responses to error rows, and an expert picks one on purpose:

1. Neutralise with try … otherwise 0 when a missing value really does mean zero for the calculation, as in a fee column where a blank means no fee.
2. Count them, because the number of bad rows is itself a data-quality signal worth reporting: Table.RowCount(Table.SelectRowsWithErrors(Typed, {"Amount"})).
3. Quarantine them by splitting the flow. Send clean rows onward with Remove Errors, and keep a parallel query of the error rows, built with Keep Errors, for someone to investigate. When the data matters, this is the professional default. Never drop a row you couldn't parse without keeping a record of it. A row you can't read is information about your source. It isn't noise to sweep away.

Build this habit. Treat an error cell as a question. Why couldn't this parse? try is how you choose your answer. Fix it now, or park it in quarantine for later. Or declare it harmless and neutralise it. The one thing you never do is let it slip unnoticed into a total.