Forum Discussion
Remove rows based on multiple criteria
Hello
What is the syntax for Power Query to remove rows on specific criteria?
I want to remove rows where (Col1 contains "something" and (Col2 is zero or null))
Thanks!
Anonymous , Filter the data based on those.
https://docs.microsoft.com/en-us/power-query/filter-values
or create column and filter that
if Text.Contains([Col1], "something") and ([Col2] =0 or [col2] = null) then 1 else 0
use filter for column , on this new column
3 Replies
Anonymous , Filter the data based on those.
https://docs.microsoft.com/en-us/power-query/filter-values
or create column and filter that
if Text.Contains([Col1], "something") and ([Col2] =0 or [col2] = null) then 1 else 0
use filter for column , on this new column
amitchandak thanks but I need the syntax in that format, can you tell me please?
Table.SelectRows(#"1", each not Text.Contains(Col.....
Hi Anonymous
In this scenario, you need to create a column then filter table by it, finally remove it.
-
here is the entire body of code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMVNJRMlSK1YlWSkpKArKNwOyqqiog2wCJnVeakwPmQrQYILHhUsnJyUCuMZidmpoKZJuA2SkpKZgmAC2KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"aaa") and ([Column2]=0 or [Column2]=null) then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}) in #"Removed Columns"the code remove rows where (Col1 contains "aaa" and (Col2 is zero or null)
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
