Forum Discussion
Solved
Unpivot and group columns
Hi everybody.
I am looking for the idea to do something like this:
I have a table:
Category
Group
Value
Cat1
Group1
323
Cat1
Group2
234
Cat2
Group3
1...
tutto fatto da GUI:
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sMVTSUXIvyi8tADGMjYyVYnVQxY2ADCNjE5i4EUzcGMgwBArHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Category " = _t, #"Group " = _t, Value = _t]), #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Value", Int64.Type}}), #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Category "}, {{"Value", each List.Sum([Value])}}), tc=Table.Combine({#"Modificato tipo",#"Raggruppate righe"}), #"Ordinate righe" = Table.Sort(tc,{{"Category ", Order.Ascending}, {"Group ", Order.Ascending}}), #"Merge di colonne" = Table.CombineColumns(#"Ordinate righe",{"Category ", "Group "},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"ID"), #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Merge di colonne", "type", each if Text.Contains(_[ID],"Group") then "Group" else "Category") in #"Aggiunta colonna personalizzata"
tutto fatto da GUI:
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sMVTSUXIvyi8tADGMjYyVYnVQxY2ADCNjE5i4EUzcGMgwBArHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Category " = _t, #"Group " = _t, Value = _t]),
#"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Value", Int64.Type}}),
#"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Category "}, {{"Value", each List.Sum([Value])}}),
tc=Table.Combine({#"Modificato tipo",#"Raggruppate righe"}),
#"Ordinate righe" = Table.Sort(tc,{{"Category ", Order.Ascending}, {"Group ", Order.Ascending}}),
#"Merge di colonne" = Table.CombineColumns(#"Ordinate righe",{"Category ", "Group "},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"ID"),
#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Merge di colonne", "type", each if Text.Contains(_[ID],"Group") then "Group" else "Category")
in
#"Aggiunta colonna personalizzata"Sorry for the delay in answering, but only today I was able to test the solution.
Anonymous , thank you very much for the code. It helps me to prepare data for visuals.
