CODEWITHSUNDEEP
powerbipowerquery
James Elderfield
James Elderfield

Reputation: 2507

How to filter Error from Power Query list

I have a list and would like to remove any that are Errors. In this particular case I have a list of tables but would prefer a generic answer.

I am currently using

RemovedErrors = List.Select(
    List.Transform(List.Positions(MyList), each try MyList{_} otherwise null),
    each _ <> null
)

but this is quite ugly, and also filters out nulls (which I might want to keep).

Upvotes: 2

Views: 7133

Answers (2)

Marc Pincince
Marc Pincince

Reputation: 5192

If, instead of using "Nulls" as my Try Otherwise default, I use something like "BadError"...like this:

enter image description here

Then I can use List.RemoveItems(#"Added Custom"[Custom],{"BadError"}) to remove the "BadError" entries from the list...like this:

enter image description here

That should leave your nulls alone.

Upvotes: 2

Alexis Olson
Alexis Olson

Reputation: 40204

Under the Home tab you can click on Remove Rows > Remove Errors.

Remove Errors

As you can see in the formula bar, the M code for this action looks like

= Table.RemoveRowsWithErrors(TableName, {"ColumnWithErrors"})

Edit: This does not work with a list. However, you can convert a list to a table using Table.FromColumns({List}) and then apply the above. You can Convert to List (under the Transform tab) if you want to switch back to a list after removing errors.

Some other possibilities are given on this community.powerbi.com thread.

Upvotes: 1

Related Questions