Forum Discussion
Recursive hierarchy in power query
Hello,
my data is:
I hve simple table like here:
EmployeeID Name ManagerID
1 Alice NULL
2 Bob 1
3 Carol 1
4 Dave 2
5 Eve 2
6 Frank 3
and need to add Level1, and Level2 and Level3 columns dynamically.
How to do this in power query ?
Best,
Jacek
jaryszek Maybe try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMyUxOBdJKsTrRSkZAhlN+EpA0BPONgSznxKL8HLiICZDlklgG0mEEFjAFslyR+GZAlltRYl42kDZWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeID = _t, #"Name " = _t, ManagerID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeID", Int64.Type}, {"Name ", type text}, {"ManagerID", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ManagerID"}, #"Changed Type", {"EmployeeID"}, "Changed Type", JoinKind.LeftOuter), #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"ManagerID"}, {"Changed Type.ManagerID"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Changed Type", {"Changed Type.ManagerID"}, #"Expanded Changed Type", {"EmployeeID"}, "Expanded Changed Type", JoinKind.LeftOuter), #"Expanded Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Changed Type", {"ManagerID"}, {"Expanded Changed Type.ManagerID"}), #"Added Custom" = Table.AddColumn(#"Expanded Expanded Changed Type", "Level", each if [ManagerID] = null and [Changed Type.ManagerID] = null then 1 else if [Changed Type.ManagerID] = null then 2 else 3), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Changed Type.ManagerID", "Expanded Changed Type.ManagerID"}) in #"Removed Columns"
2 Replies
jaryszek Maybe try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMyUxOBdJKsTrRSkZAhlN+EpA0BPONgSznxKL8HLiICZDlklgG0mEEFjAFslyR+GZAlltRYl42kDZWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeID = _t, #"Name " = _t, ManagerID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeID", Int64.Type}, {"Name ", type text}, {"ManagerID", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ManagerID"}, #"Changed Type", {"EmployeeID"}, "Changed Type", JoinKind.LeftOuter), #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"ManagerID"}, {"Changed Type.ManagerID"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Changed Type", {"Changed Type.ManagerID"}, #"Expanded Changed Type", {"EmployeeID"}, "Expanded Changed Type", JoinKind.LeftOuter), #"Expanded Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Changed Type", {"ManagerID"}, {"Expanded Changed Type.ManagerID"}), #"Added Custom" = Table.AddColumn(#"Expanded Expanded Changed Type", "Level", each if [ManagerID] = null and [Changed Type.ManagerID] = null then 1 else if [Changed Type.ManagerID] = null then 2 else 3), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Changed Type.ManagerID", "Expanded Changed Type.ManagerID"}) in #"Removed Columns"Hi jaryszek ,
Thank you for reaching out to the Microsoft fabric community forum.
Also thank you @SuperUser for the suggesting the nested join approach indeed helps trace relationships up to a certain level and is a valid start when working with fixed-depth hierarchies. However, in this specific scenario, since the number of levels may vary or require dynamically assigning each level's name (not just numeric level classification), a more flexible approach using a recursive custom function would better suit your requirement.
To implement this, you can:
- Define a recursive function in Power Query to walk up the hierarchy.
- Use that function to build a list of names in the reporting path.
- Expand those values into separate Level1, Level2, etc., columns.
Hope this helps. Please reach out for further assistance.
Thank you.
