Create hierarchies using M, not DAX

[jaryszek]

Hello, 

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 want to add hierarchy levels in power query, can anybody help with code?
I know that in DAX you can use path item but really I do not want to use it. It is calculated column, prefer to use source data.

Best,
Jacek

[burakkaragoz · Accepted solution]

Hi @jaryszek ,

Looking at the solutions provided, both approaches will work, but let me address the performance question about List.PositionOf:

About List.PositionOf vs Table.SelectRows: You're right that List.PositionOf can be faster, but it depends on your data structure. Here's a hybrid approach that combines the best of both:

// First, create lookup lists for better performance
let
    Source = your_table,
    EmployeeIDs = Source[EmployeeID],
    ManagerIDs = Source[ManagerID],
    
    // Add hierarchy level using list lookup
    GetLevel = (empID as number) as number =>
        let
            GetLevelRecursive = (currentID, level) =>
                let
                    position = List.PositionOf(EmployeeIDs, currentID),
                    managerID = if position = -1 then null else ManagerIDs{position}
                in
                    if managerID = null then level
                    else @GetLevelRecursive(managerID, level + 1)
        in
            GetLevelRecursive(empID, 0),
    
    Result = Table.AddColumn(Source, "Level", each GetLevel([EmployeeID]))
in
    Result

Performance comparison:

• List.PositionOf: Faster for lookups, but you need to manage the lookup logic
• Table.SelectRows: Slower but cleaner code and handles complex scenarios better

For your case: If you have under 10,000 employees, the performance difference won't be noticeable. Table.SelectRows is probably fine and more maintainable.

If performance is critical: Use the list-based approach above - it creates the lookup lists once and reuses them.

The recursive solutions provided by @jaineshp are solid. The List.Generate approach is particularly good for avoiding infinite loops if you have data quality issues.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[jaryszek]

Thank you for nice explanation!

[jaryszek]

Thank you. 

What about List.PositionOf function. 

It should be faster than selectRows from table? 

What do you think?

Best,
Jacek

[v-hjannapu]

Hi @jaryszek,

Thank you  for reaching out to the Microsoft fabric community forum.

Yes,  List.PositionOf can be faster in some cases because it works directly on a list, while Table.SelectRows checks every row each time. So if your data is large, using a list or record-based lookup can give better performance.

You can try both and see which one runs faster in your case.

Best Regards,
Harshitha.

[jaineshp]

Hey @jaryszek ,

Looking at your table structure, you can create hierarchy levels in Power Query using a recursive approach. Here's a concise solution:

       Step 1: Add Custom Column for Level:
          = Table.AddColumn(#"Previous Step", "Level", each
                 let
                GetLevel = (empID) =>
                let
                 manager = try Table.SelectRows(#"Previous Step", (x) => x[EmployeeID] = empID){0}[ManagerID]                               otherwise null
                     in
                   if manager = null then 0
                        else @GetLevel(manager) + 1
                          in
                     GetLevel([EmployeeID])
                        )
           Step 2: Add Full Path (Optional) If you want the complete hierarchy path:
 
                      = Table.AddColumn(#"Previous Step", "Level", each
                            List.Count(
                             List.Generate(
                                () => [ManagerID],
                                    each _ <> null,
                           each try Table.SelectRows(#"Previous Step", (x) => x[EmployeeID] = _){0}[ManagerID] otherwise null
                          )
                        )
                        )

Expected Results:

• Alice (ID 1): Level 0 (top level)
• Bob, Carol (IDs 2,3): Level 1 (report to Alice)
• Dave, Eve (IDs 4,5): Level 2 (report to Bob)
• Frank (ID 6): Level 2 (reports to Carol)

The second method handles circular references better and is more performant with larger datasets. Both will give you the hierarchy levels you need without touching DAX.

Works much better than calculated columns for performance, especially with larger datasets.

Best Regards,
Jainesh Poojara | Power BI Developer