Forum Discussion

OneWithQuestion's avatar
OneWithQuestion
Post Prodigy
8 years ago
Solved

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

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

NerfToys 
HeManToysAction
GI JoeToysAction
500 Piece TigerToysPuzzle
Star Wars ReplicaToys 
BasketBallSports 
BaseballSports 
BikeSportsOutdoor
LipstickMakeupFace
Beauty CreamMakeup 

 

 

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

HeManToysAction
GI JoeToysAction
500 Piece TigerToysPuzzle
LipstickMakeupFace

 

 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

 

ProductCategorySub Category
NerfToys 
HeManToysAction
GI JoeToysAction
500 Piece TigerToysPuzzle
Star Wars ReplicaToys 
BikeSportsOutdoor
LipstickMakeupFace
Beauty CreamMakeup 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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