Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |