filter a path with two parents

Hi mim,

could you please give an example :-)

ImkeF 
Hi, I have a similar requirement where I have multiple parents for the same child. I followed your process explained above.
However, I'm getting the below error. Can you please help me with the same? Thanks!!

An error occurred in the '' query. Expression.Error: The column 'Column1' of the table wasn't found.Details:
    Column1

Hi akhilduvvuru ,
I have change the code in the thread above. Please try again.

Hi akhilduvvuru ,
this could happen if you have a circular dependency in your data. Adding a counter to the iteration with a max of 20 would stop the iteration after this round and hopefully return the problematic rows at the bottom of the table:

(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"}}, MissingField.Ignore),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns1"}),
    Name = Table.AddColumn(#"Appended Query", "Name", each "Item" & Text.From([NodeKey])),
    InputTable = Table.Buffer(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), Counter = 0],
    each [Parent] <> null and [Parent] <> "" and [Counter] < 20,,
    each [ Parent = Table.Column(Table.SelectRows(InputTable, (ParChTable) => Record.Field(ParChTable, ChildKey) = [Parent] ), ParentKey){0},
Counter = [Counter] + 1 ],
    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"