Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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"
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.
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"
Hi @dario_cecchetti ,
In PQ:
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
@dario_cecchetti - Well, in DAX you could use GROUPBY or SUMMARIZE. In Power Query there is also a grouping feature you could use.