Forum Discussion

lkalawski's avatar
lkalawski
Resident Rockstar
5 years ago
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 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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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"

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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"
    • lkalawski's avatar
      lkalawski
      Resident Rockstar
      5 years ago

      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.

  • Smauro's avatar
    Smauro
    Solution Sage
    5 years ago

    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:

     

        #"Group per Category" = Table.Group(LastStep, {"Category"}, {
            {"Data", each 
                Table.FromRecords(
                    {[#"Group" = ")))CatTotal<<<", #"Value" = List.Sum(_[Value])]}
                    & Table.ToRecords(_[[Group],[Value]])),
                type table [Group = Text.Type, Value = Int64.Type]}
        }),
        #"Expanded Data" = Table.ExpandTableColumn(#"Group per Category", "Data", {"Group", "Value"}),
        #"Add Id" = Table.AddColumn(#"Expanded Data", "Id", each
            if [Group] = ")))CatTotal<<<" then [Category]
            else [Category] & ([Group]??""), type text),
        #"Add Type" = Table.AddColumn(#"Add Id", "Type",
            each if [Id]=[Category] then "Category" else "Group", type text),
        #"Removed Other Columns" = Table.SelectColumns(#"Add Type",{"Value", "Id", "Type"})
    in
        #"Removed Other Columns"

     

    Here's another example with Group + Combine which is a little less easy to read through:

     

        #"Group per Category" = Table.Group(LastStep, {"Category"}, {
            {"Data", each 
                Table.FromRecords(
                    {[
                        #"Type" = "Category",
                        #"Id" = [Category]{0},
                        #"Value" = List.Sum(_[Value]) ]}
                    & List.Transform(
                        Table.ToRecords(
                        Table.CombineColumns(_, {"Category", "Group"},
                        Text.Combine, "Id")
                        ),
                        each [#"Type" = "Group"] & _
                        )
                ),
                type table [Type = Text.Type, Id = Text.Type, Value = Int64.Type]}
        }),
        #"Combined Data" = Value.ReplaceType(
            Table.Combine(#"Group per Category"[Data], {"Type", "Id", "Value"}),
            type table [Type = Text.Type, Id = Text.Type, Value = Int64.Type])
    in
        #"Combined Data"

     

    Try it by replacing LastStep with your last step's name.

     

     

    Cheers