Flattening of parent-child hierarchy in power query.

[Amit_Bhaigade]

Hi Mates,

Happy new year!
I tried flattening parent child hierarchy with the help of power query and was successfully doing so. presenting solution below for larger audience so your suggestions can help me optimize it further.

Final result - 

------------------------------------------------------------------------------------------------------------------------------------------------

Query -
 

let
Source = Excel.CurrentWorkbook(){[Name="ParentChild"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Child", type text}, {"Parent", type text}}),
#"Expanded Changed Type" = let
_Hierarchy_level = 5,             // This can be parameterized to go down number of levels into hierarchy
_Generate_List = List.Generate(()=>1, each _ <= _Hierarchy_level, each _ + 1, each "Parent_" & Text.From(_))             // This generates a list like Parent_1, Parent_2,,,. till end of defined hierarchy

// Idea is to flatten the hierarchy by performing merge operation on same parent-child table multiple times

in List.Accumulate (_Generate_List, #"Changed Type" , (s, c)=>
let
_Column_headers = Table.ColumnNames(s),
_First_Column = _Column_headers{0},                        // This field will work as look-up range
_Second_Column = _Column_headers{1},                  // This field will work as look-up output
_Last_Column = List.Last(_Column_headers)             // This field will be used as look up value
in
Table.ExpandTableColumn(Table.NestedJoin(s, {_Last_Column}, #"Changed Type", {_First_Column}, "X", JoinKind.LeftOuter), "X", {_Second_Column}, {c})),

// After getting final out put in above step, I want to reverse order with few below steps so level 1, level 2 and so on can be sorted correctly

ConsolidateHierarchyWithComma = Table.CombineColumns(#"Expanded Changed Type",List.Select (Table.ColumnNames (#"Expanded Changed Type"), each Text.Contains(_,"Parent")),Combiner.CombineTextByDelimiter(", "),"Parent"),
CreateTableWithLevels = let
_Table = Table.TransformColumns (ConsolidateHierarchyWithComma, {"Parent", each Text.Split (Text.Reverse (Text.TrimEnd(_,{","," "})), " ," )}),
_MaxRecords = List.Max (Table.TransformColumns(_Table,{"Parent", each List.Count(_)})[Parent]),
_NewList = List.Generate(()=> 1, each _ <= _MaxRecords,each _ + 1)
in List.Accumulate(_NewList,_Table,(s,c)=> Table.AddColumn(s,"Level_" & Text.From(c),each try [Parent]{c-1} otherwise null)),
#"Removed Columns" = Table.RemoveColumns(CreateTableWithLevels,{"Parent"}),   // Removing hierarchy path as it is not needed now
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Child", Order.Ascending}})      // Sorting to original child order
in
#"Sorted Rows"

------------------------------------------------------------------------------------------------------------------------------------------------
I personally feel that, above solution might break if used on larger datasets due to multiple iterations. 

Would be happy to receieve a guidance.
Glad if above query helps anyone to flatten parent child hierarchy.
Cheers!

 

[AlienSx]

just for fun

let
    find_all = (name, state) => 
        [next = Record.FieldOrDefault(pairs, name),
        s = state & {name}, 
        all = if next is null then {s{0}} & List.Reverse(List.Skip(s)) else @find_all(next, state & {name})][all],
    Source = your_table,
    pairs = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(Source))), 
    lst = List.Buffer(List.Transform(Source[Child], (x) => find_all(x, {}))),
    col_names = {"Child"} & List.Skip(List.Transform({0..List.Max(List.Transform(lst, List.Count)) - 1}, (x) => "Level " & Text.From(x))),
    result = Table.FromColumns(List.Zip(lst), col_names)
in
    result

[Amit_Bhaigade]

Works like wonder with slight adjustement in below line of code -

lst = List.Buffer(List.Transform(Source[Child], (x) => List.RemoveItems (find_all(x, {}),{""}))),

[Amit_Bhaigade]

Hi @dufoq3 , this really fits the requirement!

[dufoq3]

Enjoy 🙂

[dufoq3 · Accepted solution]

Hi @Amit_Bhaigade, check this:

Output

If you want to limit hierarchy levels, you can do it in HierarchyLevelLimit step:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcs5DQAwDANALp5Lov/LIAp/GrUstdstZ4aIAHgwJCJK+atQSapUltoLnWjSoKo0qS4takqbWtKhNtwv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Child = _t, Parent = _t]),
    ReplacedBlank = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Parent"}),
    RenamedColumns = Table.RenameColumns(ReplacedBlank,{{"Child", "Name"}, {"Parent", "Value"}}),
    R = Record.FromTable(RenamedColumns),
    // Enter valid number greater than 0.
    // Default value = 0 (all levels).
    HierarchyLevelLimit = 0,
    StepBack = RenamedColumns,
    Ad_GenLev = Table.AddColumn(StepBack, "GenLev", each 
        List.Generate(
                ()=> [ y = [Name], z = {y} ],
                each [y] <> null and (if List.Contains({0, ""}, HierarchyLevelLimit) then true else List.Count([z]) <= HierarchyLevelLimit + 1),
                each [ y = Record.FieldOrDefault(R, [y], null), z = [z] & {y} ],
                each [y] ), type {text} ),
    Ad_Levels = Table.AddColumn(Ad_GenLev, "Levels", each 
        [ a = List.Zip({ {"1"..Text.From(List.Count([GenLev])-1)}, List.Reverse(List.Skip([GenLev])) }),
          b = List.Accumulate(a, #table(type table[Child=text], {{[Name]}}), (st, cur)=> Table.AddColumn(st, "Level_" & cur{0}, (x)=> cur{1}, type text))
        ][b], type table ),
    CombinedLevels = Table.Combine(Ad_Levels[Levels])
in
    CombinedLevels