Query: How to filter few conditions, delete the ones meeting criteria, and show back the others

[yetanotherexcelslave]

Hello. Say I have columns A, B, C, with the conditions I want to delete is (AND conditions, not OR):
- A = "Zebra"
- B = "Male"
- C = "Small"
I want to remove all the data that matches this requirement (means if there's a zebra, but it's a female, then I don't want to delete that data). Normally how I would do it in excel is filter each columns with the respective conditions, then delete all that are shown, and unfilter back to get the rest of the data. But since Power Query is step-by-step, I can't unfilter back after I delete the data. Does anyone know how can I do this in query?

 

[GraH]

But you can do an anti left join on the table itself by making a duplicate (before filtering).
Say you have the first Query names fAnimals and the Copy Query* (including the filters) is named FilteredSet
Then the merge is something like Table.NestedJoin(fAnimals, {"PrimaryKey"}, FilteredSet, {"PrimaryKey"}, "FilteredSet", JoinKind.LeftAnti)


Or add a custom column like:

if [A]="Zebra" and [B] = "Male" and [C]="Small" then "Out Of Scope" else "Scope"

Filter on "Scope".

* Better is to make query 1 (up to before filtering) then reference this one in a new query in which you set the filters.

 

[yetanotherexcelslave]

That works! Thanks!