Forum Discussion

amay15's avatar
amay15
Frequent Visitor
8 years ago
Solved

Filter rows query with more than 1 column condition

I want to filter data as per below table   Test Cost 1 Cost 2   Test Cost 1 Cost 2 A 0 10   A 0 10 B 0 0   C 10 0 C 10 0   E 20 20 D 0 0         E 2...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    amay15

     

    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

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

amay15

 

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

amay15's avatar
amay15
Frequent Visitor
8 years ago

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