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 1...
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"
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