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

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

I'm not sure I get the request - you need indication how to filter it in DAX for further calculations, or filter it in PowerQUery to reduce number of rows?

The DAX for this should be folllowing

Table Filtered = 
FILTER('Table','Table'[Cost 1]<>0 || 'Table'[Cost 2] <> 0)
amay15's avatar
amay15
Frequent Visitor
8 years ago

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.

  • 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

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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?