Remove or keep rows with errors (Power Query)

Data errors can occur as a result of a query or step evaluation, such as changing the type of a column to Number that results in errors for some cells. Remove errors because you want to clean up the data and the errors are not essential to fix for your purposes. But it's a good idea to keep errors (by copying the query) as an auditing tool to identify any rows with error.

For an in-depth discussion of how to deal with errors, see Dealing with errors (docs.com).

Important    Removing errors from a query does not remove or fix errors in the external data source.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Decide which columns for which you want to remove errors. The default behavior is to remove errors in all columns, but you can select a column or columns for which you want to remove errors. To remove errors from specific  columns, select the columns by using Ctrl + Click or Shift + Click. The columns can be contiguous or discontiguous.

  3. Select Home > Remove RowsRemove Errors.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Decide which columns for which you want to remove errors. The default behavior is to keep errors in all columns, but you can select a column or columns for which you want to remove errors. To remove errors from specific columns, select the columns by using Ctrl + Click or Shift + Click. The columns can be contiguous or discontiguous.

  3. Select Remove Errors.

See Also

Power Query for Excel Help

Dealing with errors (docs.com)

Need more help?

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

×