Sum pairs of rows

[dario_cecchetti]

My personal file consists of about 70 columns with an Index column.
The index column has the following sequence: [1,1,3,3,5,5 ...]

I would like to get the table with the same number of columns, but with the sum of the rows with the same index.
Below is an example of my table.

Thank you

Column1	Column2	Column3	Column4	Index
1	4	0	1	1
2	5	0	2	1
3	22	2	5	3
4	1	2	0	3
5	1	1	2	5
2	3	3	0	5

[Anonymous]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcvBCQAwCAPAXfL2o9YuI91/jRpbfATCkWRCIfCKsiiOJKzaqpgNcdHc3sQWPPtQ/GO0z3Ez+9G5", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t, Colonna3 = _t, indice = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Colonna2", Int64.Type}, {"Colonna3", Int64.Type}, {"Colonna1", Int64.Type}}),
    ncols=Table.ColumnCount(#"Modificato tipo")-2,
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"indice"}, {{"all", each List.Transform({0..ncols},(c)=> List.Sum(Table.ToColumns(_){c}))}}),
    #"Tabella all espansa" = Table.ExpandListColumn(#"Raggruppate righe", "all")
in
    #"Tabella all espansa"

prova questo.

Funziona sul presupposto ceh la colonna indice sia l'ultima e che tutte le altre n-1 colonne siano quelle da aggregare.

[Payeras_BI · Accepted solution]

Hi @dario_cecchetti ,

Yes, try this instead.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYy5DQAwCAN3oU6R8EyDsv8aIWAoToID250OLdJgBye5y4ljMlgeK39jqH+W1IooI1DWprD/u1jALnsf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Index", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Index"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

[Payeras_BI]

Hi @dario_cecchetti ,

In PQ:

[dario_cecchetti]

Hi @Payeras_BI, 

great! It works! 

however I cannot manually add 70 aggregation columns. Is there the possibility to insert them automatically?

thank you so much

[Greg_Deckler]

@dario_cecchetti - Well, in DAX you could use GROUPBY or SUMMARIZE. In Power Query there is also a grouping feature you could use.