Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
Anonymous
Not applicable

 

 

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

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

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

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors