Power Query Filter And versus Or

[Mischief63]

When using filters on columns I was taught it's better to use the text filters than unchecking items.

So my rule of thumb has always been to use "or" versus "and" because AND means both criteria have to be met to exclude the row

OK so today I have a list of fund numbers (coded as text) 1, 2, 3, 4, 5

I just want 1 and 2 - so I filter:
Fund Column equals 1
Or Fund Column equals 2

Fantastic returns only rows with 1 or 2

Now I want a query that returns rows with 3 or 4 or 5

So I duplicate the query and filter again:
Fund Column does not equal 1
OR Fund Column does not equal 2

Now returns EVERY row regardless of fund number
I have to change it to "And" to get it to return only 3,4,5

What am I missing? Shouldn't it be OR - I'm telling it to give me rows where fund does not equal 1 or does not equal 2.

 

[CyrusTheVirus]

To your last question... 'Shouldn't it be OR'... no, it shouldn't.

If you are trying to figure out if something is equal to one or two, then OR works... because 1 equals 1 OR 2 (so you'd include it), but 5 doesn't equal 1 OR 2 (so you wouldn't include it)...

... however if you want to find out if something DOESN'T equal, then the logic changes... because if you ask if 1 DOESN'T equal 1 OR 2, then you'd get true because 1 equals 1, and therefore it would include it...

... this is all because the logic changed from equals to not equals... It is kind of confusing, but just think about it some more and I'm sure it will make more sense.

Hope this helps. Good questions, reach out with any others.