Simple 'and' vs 'or' question for M code

[wwhittenton]

I realize this should be simple, but my brain is having trouble this morning.

Could someone simplify for me why the 'and' in this filters both items out

Table.SelectRows(#"Filtered rows", each [EmployeeID] <> "user1" and [EmployeeID] <> "user2")

but the 'or' in this filters out neither?

Table.SelectRows(#"Filtered rows", each [EmployeeID] <> "user1" or [EmployeeID] <> "user2")

Thanks in advance!

[watkinnc · Accepted solution]

It might be the <> operator that is tripping you up. In you first example, you say that the Employee ID does not equal "user1" AND ALSO does not equal "user2".  So everything but "user1" AND "user2" makes it through the filter (will evaluate to true).

In your second example, as the commenter correctly stated, you are writing that the Employee ID can get through the filter if it's either NOT "user1" OR NOT "user2". Power Query says let me see one of these statements are true:

Is the value not equal to "user1"? Yes? The first part of the or statement is true. Send the value through the filter, we're done. 
If the value IS = to "user1", then the <> statement is false, so we go to the next part of "or":

Is the value not equal to "user2"? If it is not equal to "user2", the statement is true, send it through the filter.

Think of or as two doors, each with a sign: YOU CAN ENTER HERE IF YOU ARE NOT USER1, and

YOU CAN ENTER HERE IF YOU ARE NOT USER2. 
See how there is a door for both users?

--Nate

[wwhittenton]

Ah, of course. The logic went right over my head. Silly mistake.

Thank you for taking the time to explain!

[Fowmy]

@wwhittenton 

When you use OR and <>, the condition will not evaluate to TRUE at any point. In other words, if you use = then you should only see either User 1 or User 2

 each [EmployeeID] = "user1" or [EmployeeID] = "user2"

[wwhittenton]

Interesting. Do you have an explanation of why that is?

Or is it easier to just say "in the future, just use AND", since that worked?

[watkinnc]

"and" will return a value if both statements are true, as in EmployeeID would equal "user1 AND also equal "user2".

"or" will return a value if EITHER statement is true, as in EMPLOYEEID can equal either "user1" OR "user2".

Hope that helps!--Nate

[wwhittenton]

Hey Nate! Thanks for the quick response.

When I used the 'or' statement above, the system failed to filter out "user1" and "user2", even though on given rows of the table containing one of the two users the 'or' seems to, logically, be verified.

I'm still in the dark about why the system, while using 'or' would say
> field contains "user1"

> field does not contain "user2"
-- the first part of the 'or' is satisfied

> system allows "user1" to stay, even though the code says to keep values not = "user1"

Have I misunderstood the code I wrote?