Group By for all the columns

[Mic1979]

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.

[Anonymous]

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]

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?

[Omid_Motamedise · Accepted solution]

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!

[Mic1979]

it works. Many thanks

[lbendlin]

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.