Creating organization hierarchy by processing data

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