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 functio...
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!

     

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?