Create hierarchies using M, not DAX

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.