Unpivot and group columns

[lkalawski]

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.

[Anonymous · Accepted 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"

[lkalawski]

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]

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