Power Query M - inable to filter out rows using AND condition (only OR)

[OneWithQuestion]

I have a table and I want to exclude rows where one column has a specific value and another column is blank.

All other rows I want to keep.

I want to exclude all rows where Category=Sports AND Sub Category ="" (blank)  (So take that subset and remove it)

My table:

Product Category Sub Category

Nerf	Toys
HeMan	Toys	Action
GI Joe	Toys	Action
500 Piece Tiger	Toys	Puzzle
Star Wars Replica	Toys
BasketBall	Sports
Baseball	Sports
Bike	Sports	Outdoor
Lipstick	Makeup	Face
Beauty Cream	Makeup

What happens if I try to filter to say  Everything BUT Sports AND Everything BUT blank

= Table.SelectRows(#"Changed Type", each ([Category] <> "Sports") and ([Sub Category] <> ""))

Product Category Sub Category

HeMan	Toys	Action
GI Joe	Toys	Action
500 Piece Tiger	Toys	Puzzle
Lipstick	Makeup	Face

 I would expect to still see all the rows that have a blank "Sub Category" but were NOT a Category of Sports.

I would also expect to see rows that are Sports but did NOT have a blank for Sub Category

Product	Category	Sub Category
Nerf	Toys
HeMan	Toys	Action
GI Joe	Toys	Action
500 Piece Tiger	Toys	Puzzle
Star Wars Replica	Toys
Bike	Sports	Outdoor
Lipstick	Makeup	Face
Beauty Cream	Makeup

[v-shex-msft · Accepted solution]

Hi @OneWithQuestion,

I'd like to suggest you to use if statement to filter rows:

Sample:

= Table.SelectRows(#"Changed Type", each if [Category]="Sports" and [Sub Category] ="" then false else true)

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY69CsIwEIBfJWR26OIDWME/rBZbcCgdznjKkdiE5DK0T2+oQ4t0uuP7PrhrGnlB/5IrWds+pCHbVSMPWEA3sY1ist1o9kdxsrio1lkmSkKFoqY3+qkp4zAYHJuKwYs7+CBu6Awp+DucQ9DIORiTSOWs57nCx6IgjXN4jfy01o/uTC4wKZ1wARqjS8sO1O+ZHCFyL7Ye4TMPZNt+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Category = _t, #"Sub Category" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Category", type text}, {"Sub Category", type text}}),
    FitlerRows = Table.SelectRows(#"Changed Type", each if [Category]="Sports" and [Sub Category] ="" then false else true)
in
    FitlerRows

BTW, I think you need to use 'OR' link your conditions, your requirement is remove rows which not matched with all conditions.

Modified formula:

 = Table.SelectRows(#"Changed Type", each [Category] <> "Sports" or [Sub Category] <> "")

Regards,

Xiaoxin Sheng