Forum Discussion
Filter rows query with more than 1 column condition
Same logic we can apply in Query Editor as well
File attached
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIAYkMDpVidaCUnKBfCc4ZIwLguKJKuQJaRAYSIjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Test = _t, #"Cost 1" = _t, #"Cost 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}, {"Cost 1", Int64.Type}, {"Cost 2", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.AllTrue({[Cost 1]=0, [Cost 2]=0})), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = false)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}) in #"Removed Columns"Bascially add a custom column to check condition
=List.AllTrue({[Cost 1]=0, [Cost 2]=0})Then filter the records
I want to apply this at query level then load the data. I dont want to create Column after data load.
Thank for quick reply. Looking forward for solution.
Community Champion
Same logic we can apply in Query Editor as well
File attached
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIAYkMDpVidaCUnKBfCc4ZIwLguKJKuQJaRAYSIjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Test = _t, #"Cost 1" = _t, #"Cost 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}, {"Cost 1", Int64.Type}, {"Cost 2", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.AllTrue({[Cost 1]=0, [Cost 2]=0})),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = false)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
#"Removed Columns"
Bascially add a custom column to check condition
=List.AllTrue({[Cost 1]=0, [Cost 2]=0})Then filter the records
Hi Zubair,
Thank you for your quick support. Appreciated!!
Question - Data load is very slow. I have huge data (approx. 10 lakh rows). Any suggestion to optimize my Query?
Regards,
Amay Singh
Thanks for your solution to OPs question. If however, one would want to add a number of conditions across the two columns, eg, in addition to 0 0, lets say also remove 20 20, what would be the added steps in the query?