Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

Group By for all the columns

Dear all,

 

I have a table with more than 20 columns, and due to some operations, I have rows duplicated that I need to sum.

 

The only thing I have in mind to solve this is with Group by function.

 

On one column the code is this:

#"Grouped Rows" = Table.Group(NewTableRule1, {"Helper_1"}, {{"OEM_Y3_Volumes", each List.Sum([OEM_Y3_Volumes]), type number}, {"DISTRIBUTOR_Y3_Volumes", each List.Sum([DISTRIBUTOR_Y3_Volumes]), type number}})

 

Is there any way to manage this for all the columns instead of having all the columns listed here:

{"Helper_1"}

 

Thanks for your feedback.

 

Need Help
  • Omid_Motamedise's avatar
    Omid_Motamedise
    1 year ago

    Use this formula

     

    = Table.Group(Source, {"Helper_1"}, {{"Count", each [s1=Table.UnpivotOtherColumns(_,{"Helper_1"},"A","B"),s2=Table.Pivot(s1, List.Distinct(s1[A]), "A", "B", List.Sum)][s2]}})

     

     

    If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!

     

5 Replies

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    Use this formula

     

    = Table.Group(Source, {"Helper_1"}, {{"Count", each [s1=Table.UnpivotOtherColumns(_,{"Helper_1"},"A","B"),s2=Table.Pivot(s1, List.Distinct(s1[A]), "A", "B", List.Sum)][s2]}})

     

     

    If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!

     

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Do you really need to do that in Power Query?  If you are summing this up then let Power BI do it for you, it automatically aggregates.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Mic1979 ,

     

    How about selecting the “Helper_1” column first, then unpivoting the other columns, and finally pivoting the columns again?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0BBLGxkDC1BRImJsDCUtLpVgdiLSREZAwMQESZmZAwsICIe2EVSOKNJpGQ0MDhLwzpk5DI0NUeXSthsZGUAWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Helper_1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Helper_1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Helper_1"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum 

    • Mic1979's avatar
      Mic1979
      Post Partisan
      1 year ago

      Thanks for your answers.

       

      I thought to this solution instead:

       

      ColumnsToGroup = List.Difference (Table.ColumnNames(NewTableRule1), {"OEM_Y3_Volumes","DISTRIBUTOR_Y3_Volumes"}),

       

      #"Grouped Rows" = Table.Group(#"Summary_Volumes (2)", ColumnsToGroup ,

      {{"OEM_Y3_Volumes", each List.Sum([OEM_Y3_Volumes]), type number},

      {"DISTRIBUTOR_Y3_Volumes", each List.Sum([DISTRIBUTOR_Y3_Volumes]), type number}})

       

      What do you think?