Create extra columns showing hierarchies in Power Query

[cmc099]

Hi there, I'm having an issue changing a table and adding columns to display the hierarchy of groups in PowerQuery. Below is an example of my data.

Group name	Parent group name	Group level
All	NULL	1
Directors	All	2
Sales	All	2
Region_sales	Sales	3
City_sales	Region_sales	4

The Group name column shows the group name for a specific row, the Parent group name column shows the group name that is directly above the row's group, and the Group level shows the depth of the group in the company. 

What I want to do is create 4 extra columns that match the row's group name with all of the different parent groups and sub-groups, showing the levels they are at. So, for example, the above table would look like this:

Group name	Parent group name	Group level	Level_1	Level_2	Level_3	Level_4
All	NULL	1	All	NULL	NULL	NULL
Directors	All	2	All	Directors	NULL	NULL
Sales	All	2	All	Sales	NULL	NULL
Region_sales	Sales	3	All	Sales	Region_sales	NULL
City_sales	Region_sales	4	All	Sales	Region_sales	City_sales

I'm finding it quite difficult to create these columns however, and another thing to consider, is that I want to automatically detect how many columns should be added based on the Group level. This way, if another subgroup was created making the maximum depth equal to 5, then 5 columns would be added to represent the 5 levels. Any help with this would be greatly appreciated, thank you. 

[v-cgao-msft]

Hi @cmc099 ,

Hope this helps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszJUdJR8gv18QFShkqxOtFKLplFqckl+UXFQBGItBFYPDgxJxVdLCg1PTM/L74YKgVTYgyWdM4sqYRLoak0UYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Group name" = _t, #"Parent group name" = _t, #"Group level" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group name", type text}, {"Parent group name", type text}, {"Group level", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Level", each List.Transform({List.Min(#"Changed Type"[Group level])..List.Max(#"Changed Type"[Group level])},each "Level_" &Text.From( _))),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
    #"Expanded Level" = Table.ExpandListColumn(#"Added Index", "Level"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Level", "Custom", each let 
a = Number.From(Text.Range([Level],Text.PositionOf([Level],"_")+1)),
b = 
if a = 1 then "All"
else if [Group level] < a then "NULL"
else if [Group level] = a then [Group name]
else if [Group level] - a = 2 then 
let 
c = [Parent group name] ,
d = Table.SelectRows(#"Changed Type",(x)=>x[Group name]=c)[Parent group name]{0}
in
d
else if [Group level] - a = 1 then [Parent group name]
else null 
in
b),
    #"Pivoted Column" = Table.Pivot(#"Added Custom1", List.Distinct(#"Added Custom1"[Level]), "Level", "Custom"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum