cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lkalawski
Super User II
Super User II

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.

1 ACCEPTED SOLUTION
Rocco_sprmnt21
Super User II
Super User II

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"

View solution in original post

3 REPLIES 3
Rocco_sprmnt21
Super User II
Super User II

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"

View solution in original post

Sorry for the delay in answering, but only today I was able to test the solution.

@Rocco_sprmnt21 , thank you very much for the code. It helps me to prepare data for visuals.

Smauro
Super User I
Super User I

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




Feel free to connect with me:
LinkedIn

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors