filter a path with two parents

Hi mim,

this is a variation of my pattern to dynamically flatten parent-child-hierarchies. I've twitched it a bit to cover multiple parents as well. Cannot see any flaws at the moment, but you'd do me a favour to test it thoroughly. Would like to write a post for this forum here, if it works successfully, as I think that this would be useful for others as well :-)

(TableName, ParentColumnName as text, ChildColumnName as text) =>

let

// Prepare input data
    ParChTable = TableName,
    #"Renamed Columns1" = Table.RenameColumns(ParChTable,{{ChildColumnName, "NodeKey"}, {ParentColumnName, "ParentKey"}}),
    Custom1 = List.Difference(List.Distinct(#"Renamed Columns1"[ParentKey]), List.Distinct(#"Renamed Columns1"[NodeKey])),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "NodeKey"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns1"}),
    Name = Table.AddColumn(#"Appended Query", "Name", each "Item" & Text.From([NodeKey])),
    InputTable = Table.AddIndexColumn(Name, "Index", 0, 1),
    ChildKey = "NodeKey",
    ParentKey = "ParentKey",
    LevelColumnName = "Name",

// Retrieve all parents per row
    fnAllParents = Table.AddColumn(InputTable, "AllParents", each List.Generate(()=>
    [Parent=Record.Field(_, ParentKey)],
    each [Parent] <> null and [Parent] <> "",
    each [ Parent = Table.Column(Table.SelectRows(InputTable, (ParChTable) => Record.Field(ParChTable, ChildKey) = [Parent] ), ParentKey){0} ],
    each [Parent])),
// Calculate Max Browse Depth as parameter
    MaxBrowseDepth = List.Max(List.Transform(fnAllParents[AllParents], each List.Count(_)))+1,
// Collect and sort all items for PathItems
    PathItems = Table.AddColumn(fnAllParents, "PathItems", each List.Union({List.Sort([AllParents]), {Record.Field(_, ChildKey)}})),
// Hierarchy Depth of current row
    HierarchyDepth = Table.AddColumn(PathItems, "HierarchyDepth", each List.Count([AllParents])+1),
// Check if is leaf
    Leaf = Table.AddColumn(HierarchyDepth, "IsLeaf", each if [HierarchyDepth]=MaxBrowseDepth then true else false),
// Create String Hiearchy Path (PathItems equivalent)
    HierarchyPath = Table.AddColumn(Leaf, "HierarchyPath", each Text.Combine(List.Transform([PathItems], each Text.From(_)), "|")),
// Create table for pivoting levels
    PivotTable = Table.AddColumn(HierarchyPath, "PivotTable", each Table.AddIndexColumn(Table.FromColumns({List.Union({[PathItems], List.Repeat({List.Last([PathItems])}, MaxBrowseDepth-[HierarchyDepth]+1)})}), "Level",1,1)),
// Expand pivot-level table
    #"Expanded PivotTable" = Table.ExpandTableColumn(PivotTable, "PivotTable", {"Column1", "Level"}, {"Column1", "Level"}),
// Lookup values from the name column for the level-pivots
    MergeForAccountName = Table.NestedJoin(#"Expanded PivotTable",{"Column1"},InputTable,{ChildKey},"NewColumn",JoinKind.LeftOuter),
// Expand name column
    ExpandAccountname = Table.ExpandTableColumn(MergeForAccountName, "NewColumn", {LevelColumnName}, {"LevelColumnName"}),
// Cleanup
    #"Removed Columns" = Table.RemoveColumns(ExpandAccountname,{"Column1", "AllParents", "PathItems"}),
// Create helper column for pivot column names
    AddLevel = Table.AddColumn(#"Removed Columns", "Merge", each "Level "),
    // Merge for pivot column names
    MergeLevel = Table.CombineColumns(Table.TransformColumnTypes(AddLevel, {{"Level", type text}}, "de-DE"),{"Merge", "Level"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Level" as text),
// New step: Remove dups
    #"Removed Duplicates" = Table.Distinct(MergeLevel, {"NodeKey", "ParentKey", "HierarchyPath", "Level"}),
// Create dynamic number of level-columns by pivoting
    #"Pivoted Column1" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Level]), "Level", "LevelColumnName")
in
    #"Pivoted Column1"

Link to file: https://www.dropbox.com/s/pqaxcy4e35t6oe5/DynamicPCHierarchy.pbix?dl=0