Creating organization hierarchy by processing data

[dasieakr]

Hi All,

As I mentioned I need to process my table to receive organization hierarachy. My table contains two columns: Structure_Element with the name of current position and numerical Level.

Level	Structure_Element
1	Example1Level1
2	Example1Level2
3	Example1Level3
4	Example1Level4
6	Example1Level6
6	Example2Level6
4	Example2Level4
6	Example3Level6
6	Example4Level6

There are two points worth to mention:

- In this hierarchy level 6 stands for accounts. It is the smalles unit in this hierarchy and majority of levels are at level 6th.

- Sometimes there are gaps between two levels. As you can see above there is no level 5 between level 4 and 6. This gap needs to be filled with last existing level so in this case level 4 needs to be doubled.

So far I have been using VBA makro that depends on nested loops and array as a list of levels which is updating after each iteration. This makro processed table to the following structure.

Level1	Level2	Level3	Level4	Level5	Level6
Example1Level1	Example1Level2	Example1Level3	Example1Level4	Example1Level4	Example1Level6
Example1Level1	Example1Level2	Example1Level3	Example1Level4	Example1Level4	Example2Level6
Example1Level1	Example1Level2	Example1Level3	Example2Level4	Example2Level4	Example3Level6
Example1Level1	Example1Level2	Example1Level3	Example2Level4	Example2Level4	Example4Level6

I've tried to recreate this VBA makro in Power Query by preparing function using List.Accumulate and List.Generate but without luck. 

Could you help me with this? Is there a possibility to do this this way or should I try something else. As a beginner this issue is realy mindblowing for me.

[mahoneypat]

Please see this article on how to do this with the PATH() function.

https://powerpivotpro.com/2017/12/imagine-people-tables/

Regards,

Pat

[AlB · Accepted solution]

Hi @dasieakr 

Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXKtSMwtyEk19EktS80xVIrViVYyQhc2Agsbowsbg4VN0IVNwMJm6MJm6MJGSMIm6MIYhhhjN8QEJhwLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level = _t, Structure_Element = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Structure_Element", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Level", Order.Ascending}, {"Structure_Element", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Level"}, {{"Values", each [Structure_Element], type table [Level=nullable number, Structure_Element=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "List size", each List.Count([Values]), Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Filled-up list", each let
        NumItems = List.Max(#"Added Custom"[List size]),
        base = [Values],
        lenBase = List.Count([Values]),
        toFill = List.Accumulate(List.Numbers(0,NumItems-List.Count(base)),{},(s,c)=> s & {base{Number.Mod(c,lenBase)}}),
        res = base & toFill
    in 
        res),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Values", "List size"}),
    missingLevels = List.Difference(List.Numbers(1,6), #"Grouped Rows"[Level]),
    levelsToFill = List.Transform(missingLevels, each _-1),
    rowsToFill = Table.SelectRows(#"Removed Columns", each List.Contains(levelsToFill, [Level])),
    rowsToFill2 = Table.TransformColumns(rowsToFill, {"Level", each _+1}),
    filledTable = Table.Sort(Table.Combine({#"Removed Columns", rowsToFill2}),{{"Level", Order.Ascending}}) ,
    #"Extracted Values" = Table.TransformColumns(filledTable, {"Filled-up list", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values", "Custom", each "Level" & Text.From([Level])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Level"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Level"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Level", "Filled-up list"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Filled-up list", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Filled-up list.1", "Filled-up list.2", "Filled-up list.3", "Filled-up list.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Filled-up list.1", type text}, {"Filled-up list.2", type text}, {"Filled-up list.3", type text}, {"Filled-up list.4", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers