cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dario_cecchetti
Regular Visitor

Sum pairs of rows

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

Column1Column2Column3Column4Index
14011
25021
322253
41203
51125
23305
1 ACCEPTED SOLUTION
Payeras_BI
Super User I
Super User I

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"

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

5 REPLIES 5
Rocco_sprmnt21
Super User II
Super User II

 

 

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
Super User I
Super User I

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"

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

Payeras_BI
Super User I
Super User I

Hi @dario_cecchetti ,

In PQ:

Payeras_BI_0-1614003882687.png

Payeras_BI_1-1614003897587.png

 

 

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

dario_cecchetti_0-1614004977120.png

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
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors