Recursive hierarchy in power query

[jaryszek]

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

[Anonymous]

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:

1. Define a recursive function in Power Query to walk up the hierarchy.
2. Use that function to build a list of names in the reporting path.
3. Expand those values into separate Level1, Level2, etc., columns.

Hope this helps. Please reach out for further assistance.

Thank you.

[Greg_Deckler · Accepted solution]

@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"