Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | 14 |
and I want to convert to this:
Type | Id | Value |
Category | Cat1 | 557 |
Group | Cat1Group1 | 323 |
Group | Cat1Group2 | 234 |
Category | Cat2 | 14 |
Group | Cat2Group3 | 14 |
I know that the first step should be unpivot table to have Type in the column, I don't know how I should prepare the code to generate Id (it should be a condition: if type is category then id = category name else if type is group then combine text category name + group name). The same with the Value (it should be sum of the combining).
Thank you in advance.
Solved! Go to Solution.
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.
Hi @lkalawski
Personally, I would do it with group + expand, because you do need the total sum of the Category as well.
Here's an example:
Here's another example with Group + Combine which is a little less easy to read through:
Try it by replacing LastStep with your last step's name.
Cheers
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.