Forum Discussion

lavanyak's avatar
lavanyak
Icon for Advocate I rankAdvocate I
8 years ago

Sumif in Table.group power query

Hello,

 

How can I use SUMIF in Table.Group, i,e I want to group the data conditionally.Below is the sample data and output I need:

 

Group SalesFlag
A10001
A5000
A10001
B14001
B16001
C50001

For this data, I need sum of sales by Group where Flag is 1;

 

Output I need is:

GroupSalesFlag
A20001
B30001
C50001

 

 

We have something like this 

= Table.Group(Rategain_PivotN,"Join_key",{"AvgCheapestPrice-EZ", each List.SUM([Cheapest Price])})

 

But I need something which will allow me to group data by join _key and sum based of flag=1.So is there a function called List.Sumif in power query where I can use it in Table.group.

 

 

Regards

Lavanya

 

 

 

Need Help

6 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    Hi lavanyak,

     

    In Power Query you should first filter the Flag for values equal 1 and then make the group by Group.

     

    This will give the expected result.

     

    Regards,

    MFelix

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Could you simply filter out anything with FLAG = 0 on the previous statement, then use LIST.SUM?

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi lavanyak,

     

    Based on my test, we can use Group By feature in power query. Firstly, we need filter the table, after that we can use the group By function to  get the result as below.

     

     

    The backend M formula is below:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MDAAUUqxOhABUzDfAM5HUeAEYpmgC5ghCThDjIAKxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Group " = _t, Sales = _t, Flag = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group ", type text}, {"Sales", Int64.Type}, {"Flag", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Flag] = 1)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Group "}, {{"Count", each List.Sum([Sales]), type number}, {"flag", each List.Max([Flag]), type number}})
in
#"Grouped Rows"

     

    For more details, please check the pbix as attached.

     

    https://www.dropbox.com/s/om76kw1yrejkjl4/Sumif%20in%20Table.group%20power%20query.pbix?dl=0

     

    Regards,

    Frank

    • lavanyak's avatar
      lavanyak
      Icon for Advocate I rankAdvocate I
      8 years ago

      Thanks for your response.

       

      I need something like List.SUMIF, because for few fields I need total sum irrespective of whatever value flag takes and few other fields I need sum where flag is 1.

       

      If I filter the records bfr grouping then I wont be able to get sum of all values where flag is 1,2,3 etc.

       

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi lavanyak,

         

        Here we can add a conditional column in advance to get all the values of flag you need.

         

        = Table.AddColumn(#"Changed Type", "Custom", each if [Flag] <> 0 then [Sales] else null)

         

        After that, we can we can use Group By function as before.

         

         

        The backend M formula is below:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MDAAUUqxOhABUzDfAM6HKjACCziBBEyQdIAFzJBUOEOMgKqIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Group " = _t, Sales = _t, Flag = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group ", type text}, {"Sales", Int64.Type}, {"Flag", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Flag] <> 0 then [Sales] else null),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Group "}, {{"Count", each List.Sum([Sales]), type number}, {"asd", each _, type table}})
in
    #"Grouped Rows"

        For more details, please check the pbix as attached.

         

        https://www.dropbox.com/s/f9gcjkbtt7r5zhy/Sumif%20in%20Table.group%20power%20query2.pbix?dl=0

         

        Regards,

        Frank