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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors