'Filtering Rows' step does not work for 'Not Equals' condition in conjunction with an OR operator

[adurham1920]

Hi All,

I've encountered an issue with the Power Query editor when trying to filter rows using the "OR" operation with the "Not Equals" Conditional logic. This is a fundamental boolean logic issue - so I'm concerned given that this seems to be replicable in Excel and Power BI Desktop.

Sample data/Power Query Script below:

ID	Name
110	A
112	B
113	A
114	C
118	C
200	C
204	A
290	E
492	D
921	B

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0UNJRclSK1QGxjYBsJyjbGEncBMh2hrIt4GwjAxO4GksjQ7heE0uQOS4QNZYg810x1BsZGEDMiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [ID] <> 112 or [ID] <> 204)
in
    #"Filtered Rows"

I've attached the data where the issue is reproducable, and I'm not sure where I am going wrong. 

I separated 3 tables to distinguish where the issue seems to occur - and where it doesn't. I am able to filter these rows out by using AND or individually filtering each row.

The issue occurs when I want to filter multiple numbers based upon a predefined condition. In the example, I am trying to exclude the records where the ID column is not the numeric values (112 or 204).

Based on the documentation here: Operators  and Logical Functions Reference :The OR Function should exclude a ROW if any argument matches the condition that it is not equal to 112 or 204. This does not match the expected output, as my initial 11 rows should be filtered down to 8. 

Expected output on the filter:

ID	Name
110	A
113	A
114	C
118	C
200	C
290	E
492	D
921	B

[mussaenda · Accepted solution]

Hi @adurham1920 

Usually, if you are using <> for filtering, you must use "and" 

Hope this helps

[olgad]

I will try to think it over, 
but in Power Query I just know that when you have all values ticked and you untick some then it is always AND , 
when you have all values unticked and you tick some it is always OR.
So basically if you wanna filter for several values it is OR, when you wanna filter out it is AND. 
I want this OR that, i dont want this AND that basically:)

[olgad]

Hi there,
use and not or

[adurham1920]

Hi Olgad, 

Thanks for your response!

I understand that there is a simple workaround for this issue by using the "AND" condition, or by individually filtering each row. My question is centered around understanding why this applied step doesn't work - because logically it should. An OR Boolean condition should return TRUE where: 1 of the matching conditions has been found. In this case both of the logical conditions are true, but the rows do not return filtered on matching conditions and it appears to be a bug. I've used this type of filter in the past and have not encountered an issue, so I'm wondering why it's changed now.