(ParChTable as table, ChildKey as text, ParentKey as text, LevelColumnName as text) => let // Retrieve all parents per row fnAllParents = Table.AddColumn(ParChTable, "AllParents", each List.Generate(()=> [Parent=Record.Field(_, ParentKey)], each [Parent] <> null and [Parent] <> "", each [ Parent = Table.Column(Table.SelectRows(ParChTable, (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.Reverse([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 not List.Contains(Table.Column(HierarchyDepth, ParentKey), Record.Field(_,ChildKey))), // 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({[PathItems]}), "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"},Table.Distinct(ParChTable, {ChildKey}),{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), // Create dynamic number of level-columns by pivoting #"Pivoted Column1" = Table.Pivot(MergeLevel, List.Distinct(MergeLevel[Level]), "Level", "LevelColumnName") in #"Pivoted Column1"