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"
