Implementing Custom Hierarchical Data Flattening in Power Query M: Recursive Parent-Child Resolution and Path Enumeration for Organizational and Category Trees

Introduction

Picture this: your company's HR system exports an organizational chart as a flat table — each row is an employee with a EmployeeID and a ManagerID column pointing to their boss. Sounds manageable, until your VP of Finance asks for a report that shows the full reporting chain from the CEO down to each individual contributor, with a calculated depth level and a breadcrumb path like CEO > CFO > Finance Director > Analyst. Your flat table has no idea how deep the tree goes, and the standard Power Query interface offers no built-in way to walk parent-child relationships recursively.

This is the parent-child hierarchy problem, and it's everywhere in real-world data. Product category trees in e-commerce databases, account hierarchies in ERP systems, folder structures in document management systems, geographic region rollups — they all share the same shape: a self-referencing table where each row knows its parent, but the full ancestry chain must be assembled by repeatedly walking up the tree. Power Query M is fully capable of solving this, but it requires you to write recursive logic yourself using functions that call themselves.

By the end of this lesson, you'll be able to take any self-referencing parent-child table and produce a flattened output with resolved ancestor paths, depth levels, and hierarchical breadcrumbs — entirely in Power Query M. No DAX, no helper tables, no SQL CTEs required.

What you'll learn:

• How parent-child hierarchies are structured and why they're hard to flatten without recursion
• How to write a recursive M function that walks a tree from any node up to the root
• How to enumerate full ancestor paths (e.g., "Region > Country > City") for each row
• How to compute depth levels dynamically regardless of how many levels the tree has
• How to handle edge cases like missing parents, circular references, and variable-depth trees

Prerequisites

You should be comfortable writing basic M expressions in the Power Query Advanced Editor. If you're new to M syntax, start with the M Language Fundamentals: Syntax, Types, and Expressions for Power Query lesson before continuing. You should also understand how lists and records work in M — the List and Record Operations in M: Transform, Select, and Combine Data Structures article is the right foundation there.

---

Understanding the Shape of the Problem

Let's work with a concrete dataset. Imagine you have an organizational hierarchy from a company called Meridian Analytics. The raw export from their HR system looks like this:

EmployeeID	Name	Title	ManagerID
1	Sandra Okafor	CEO	null
2	Marcus Webb	CFO	1
3	Priya Nair	CTO	1
4	Devon Castillo	Finance Director	2
5	Yuki Tanaka	Engineering Manager	3
6	Lena Hoffmann	Senior Analyst	4
7	Raj Patel	Software Engineer	5
8	Chloe Dupont	Junior Analyst	6

Sandra is the root — she has no manager. Everyone else references someone above them. To get Chloe's full path, you'd need to trace: Chloe → Lena → Devon → Marcus → Sandra. That's four hops, and you have to look up each one in the table.

This is a self-referencing table, sometimes called an adjacency list. It's the most common way to store hierarchical data in relational databases because it's compact and easy to update. The downside is that flattening it requires traversal logic that standard SQL and most BI tools don't handle elegantly without special extensions.

Note: The number of levels in this kind of table is rarely fixed. One branch might have 3 levels, another might have 7. Any solution that hardcodes a specific number of levels will break as the organization changes.

---

Building the Core Recursive Lookup Function

The key insight is this: to find the ancestors of any node, you need a function that accepts a node's ID, looks up its parent, and then calls itself on the parent — repeating until it hits a root node with no parent. This is recursion: a function that calls itself, with a stopping condition to prevent infinite loops.

In Power Query M, you can write recursive functions using the let expression with a named function that references itself. If you want to understand how M evaluates these expressions lazily, the Understanding M Language Query Evaluation: Lazy Evaluation, Dependency Graphs, and Step Ordering in Power Query article explains the evaluation model in depth.

Let's build this step by step. First, open a blank query in Power Query by going to Home tab → New Source → Blank Query, then open the Advanced Editor.

Here's the foundational query — paste in the raw data first:

let
    // Source data representing the org chart
    Source = Table.FromRows(
        {
            {1, "Sandra Okafor",  "CEO",                 null},
            {2, "Marcus Webb",    "CFO",                 1},
            {3, "Priya Nair",     "CTO",                 1},
            {4, "Devon Castillo", "Finance Director",    2},
            {5, "Yuki Tanaka",    "Engineering Manager", 3},
            {6, "Lena Hoffmann",  "Senior Analyst",      4},
            {7, "Raj Patel",      "Software Engineer",   5},
            {8, "Chloe Dupont",   "Junior Analyst",      6}
        },
        type table [EmployeeID = Int64.Type, Name = text, Title = text, ManagerID = nullable Int64.Type]
    )
in
    Source

Now we need a lookup mechanism. The most efficient approach is to convert the table to a list of records once, so that lookup operations don't re-evaluate the full table repeatedly:

let
    Source = /* ... same as above ... */,

    // Convert to a list of records for O(1)-style lookup
    EmployeeList = Table.ToRecords(Source),

    // Helper: find a single employee record by ID
    FindEmployee = (id as nullable number) as nullable record =>
        let
            Matches = List.Select(EmployeeList, each _[EmployeeID] = id)
        in
            if List.IsEmpty(Matches) then null else List.First(Matches)

in
    FindEmployee(4)  // Should return Devon Castillo's record

Tip: Converting your table to a list of records with Table.ToRecords before running recursive lookups is an important performance consideration. Each call to FindEmployee scans the list, but at least it's scanning a materialized list rather than triggering a full table re-evaluation. For very large hierarchies (thousands of nodes), consider using Table.Buffer on the source table first.

---

Writing the Recursive Ancestor Function

Now for the heart of the solution — a recursive function that collects all ancestors of a given employee as a list, walking from the node all the way to the root:

let
    Source = /* ... source data ... */,
    EmployeeList = Table.ToRecords(Source),

    FindEmployee = (id as nullable number) as nullable record =>
        let
            Matches = List.Select(EmployeeList, each _[EmployeeID] = id)
        in
            if List.IsEmpty(Matches) then null else List.First(Matches),

    // Recursive function: returns a list of Names from root down to current node
    GetAncestorPath = (id as nullable number) as list =>
        let
            CurrentEmployee = FindEmployee(id),
            HasParent = CurrentEmployee <> null and CurrentEmployee[ManagerID] <> null
        in
            if CurrentEmployee = null then
                {}  // Node not found — return empty list
            else if not HasParent then
                { CurrentEmployee[Name] }  // Root node — list containing just this name
            else
                // Recursively get the parent's path, then append this node's name
                GetAncestorPath(CurrentEmployee[ManagerID]) & { CurrentEmployee[Name] }

in
    GetAncestorPath(8)  // Should return {"Sandra Okafor", "Marcus Webb", "Devon Castillo", "Lena Hoffmann", "Chloe Dupont"}

Let's trace through what happens when you call GetAncestorPath(8) for Chloe Dupont:

1. Look up employee 8 (Chloe). Her ManagerID is 6 (Lena). HasParent is true.
2. Recursively call GetAncestorPath(6) — look up Lena. Her ManagerID is 4. HasParent is true.
3. Recursively call GetAncestorPath(4) — look up Devon. Her ManagerID is 2. HasParent is true.
4. Recursively call GetAncestorPath(2) — look up Marcus. His ManagerID is 1. HasParent is true.
5. Recursively call GetAncestorPath(1) — look up Sandra. Her ManagerID is null. HasParent is false. Return {"Sandra Okafor"}.
6. Unwind: Return {"Sandra Okafor", "Marcus Webb"}.
7. Unwind: Return {"Sandra Okafor", "Marcus Webb", "Devon Castillo"}.
8. Unwind: Return {"Sandra Okafor", "Marcus Webb", "Devon Castillo", "Lena Hoffmann"}.
9. Final unwind: Return {"Sandra Okafor", "Marcus Webb", "Devon Castillo", "Lena Hoffmann", "Chloe Dupont"}.

The & operator joins lists together, so each level of the recursion appends its own name to the list returned by the deeper call.

Warning: M does not have a native recursion depth limit that you control directly, but you will hit a stack overflow error if your hierarchy is extremely deep (typically beyond a few hundred levels) or if you accidentally create a circular reference. We'll address circular reference protection later in this lesson.

---

Applying the Function Across the Entire Table

A single lookup is satisfying, but you need this applied to every row. The pattern for this is Table.AddColumn combined with your recursive function. Writing modular, reusable functions like this is a skill covered in depth in Writing Custom M Functions from Scratch in Power Query.

let
    Source = Table.FromRows(
        {
            {1, "Sandra Okafor",  "CEO",                 null},
            {2, "Marcus Webb",    "CFO",                 1},
            {3, "Priya Nair",     "CTO",                 1},
            {4, "Devon Castillo", "Finance Director",    2},
            {5, "Yuki Tanaka",    "Engineering Manager", 3},
            {6, "Lena Hoffmann",  "Senior Analyst",      4},
            {7, "Raj Patel",      "Software Engineer",   5},
            {8, "Chloe Dupont",   "Junior Analyst",      6}
        },
        type table [EmployeeID = Int64.Type, Name = text, Title = text, ManagerID = nullable Int64.Type]
    ),

    EmployeeList = Table.ToRecords(Table.Buffer(Source)),

    FindEmployee = (id as nullable number) as nullable record =>
        let
            Matches = List.Select(EmployeeList, each _[EmployeeID] = id)
        in
            if List.IsEmpty(Matches) then null else List.First(Matches),

    GetAncestorPath = (id as nullable number) as list =>
        let
            CurrentEmployee = FindEmployee(id),
            HasParent = CurrentEmployee <> null and CurrentEmployee[ManagerID] <> null
        in
            if CurrentEmployee = null then {}
            else if not HasParent then { CurrentEmployee[Name] }
            else GetAncestorPath(CurrentEmployee[ManagerID]) & { CurrentEmployee[Name] },

    // Add a column containing the full path as a list
    WithPathList = Table.AddColumn(Source, "AncestorPathList", each GetAncestorPath([EmployeeID]), type list),

    // Convert the list to a readable breadcrumb string
    WithPathString = Table.AddColumn(WithPathList, "FullPath", each Text.Combine([AncestorPathList], " > "), type text),

    // Add a depth level column (root = 0)
    WithDepth = Table.AddColumn(WithPathString, "Depth", each List.Count([AncestorPathList]) - 1, Int64.Type),

    // Clean up intermediate column
    FinalTable = Table.RemoveColumns(WithDepth, {"AncestorPathList"})

in
    FinalTable

The output will look like this:

EmployeeID	Name	Title	ManagerID	FullPath	Depth
1	Sandra Okafor	CEO	null	Sandra Okafor	0
2	Marcus Webb	CFO	1	Sandra Okafor > Marcus Webb	1
4	Devon Castillo	Finance Director	2	Sandra Okafor > Marcus Webb > Devon Castillo	2
8	Chloe Dupont	Junior Analyst	6	Sandra Okafor > Marcus Webb > Devon Castillo > Lena Hoffmann > Chloe Dupont	4

Notice Table.Buffer(Source) in the EmployeeList step. This materializes the source table into memory before GetAncestorPath starts looping over it, which prevents repeated re-evaluation of the source. This pattern is discussed in M Language Performance Patterns and Anti-Patterns: Optimize Power Query for Speed.

---

Adding Individual Level Columns

Sometimes you don't just want a breadcrumb string — you need the hierarchy broken into discrete Level 1, Level 2, Level 3 columns, which is what Power BI's hierarchy visuals typically expect. You can derive these from your path list:

    // After WithPathList is defined...
    MaxDepth = List.Max(Table.Column(WithPathList, "AncestorPathList"), each List.Count(_)),

    // Dynamically generate level columns based on actual max depth
    LevelColumns = List.Generate(
        () => 0,
        each _ <= MaxDepth,
        each _ + 1,
        each (i) => Table.AddColumn(
            /* previous table */,
            "Level" & Text.From(i + 1),
            each if List.Count([AncestorPathList]) > i then [AncestorPathList]{i} else null,
            type nullable text
        )
    )

Key insight: The dynamic column generation approach above avoids hardcoding "Level1", "Level2", etc. This matters because if your deepest branch grows from 5 to 7 levels next quarter, your query adapts without any manual changes. This is the kind of forward-thinking design that separates robust pipelines from brittle ones.

Here's the complete version that generates level columns dynamically:

let
    Source = Table.FromRows(
        {
            {1, "Sandra Okafor",  "CEO",                 null},
            {2, "Marcus Webb",    "CFO",                 1},
            {3, "Priya Nair",     "CTO",                 1},
            {4, "Devon Castillo", "Finance Director",    2},
            {5, "Yuki Tanaka",    "Engineering Manager", 3},
            {6, "Lena Hoffmann",  "Senior Analyst",      4},
            {7, "Raj Patel",      "Software Engineer",   5},
            {8, "Chloe Dupont",   "Junior Analyst",      6}
        },
        type table [EmployeeID = Int64.Type, Name = text, Title = text, ManagerID = nullable Int64.Type]
    ),

    Buffered = Table.Buffer(Source),
    EmployeeList = Table.ToRecords(Buffered),

    FindEmployee = (id as nullable number) as nullable record =>
        let Matches = List.Select(EmployeeList, each _[EmployeeID] = id)
        in if List.IsEmpty(Matches) then null else List.First(Matches),

    GetAncestorPath = (id as nullable number) as list =>
        let
            Emp = FindEmployee(id),
            HasParent = Emp <> null and Emp[ManagerID] <> null
        in
            if Emp = null then {}
            else if not HasParent then { Emp[Name] }
            else GetAncestorPath(Emp[ManagerID]) & { Emp[Name] },

    WithPathList = Table.AddColumn(Buffered, "PathList", each GetAncestorPath([EmployeeID]), type list),
    WithDepth    = Table.AddColumn(WithPathList, "Depth", each List.Count([PathList]) - 1, Int64.Type),
    WithFullPath = Table.AddColumn(WithDepth, "FullPath", each Text.Combine([PathList], " > "), type text),

    // Find the maximum depth across all employees
    MaxDepth = List.Max(Table.Column(WithDepth, "Depth")),

    // Build level columns one at a time using List.Accumulate
    LevelAdded = List.Accumulate(
        { 0 .. MaxDepth },
        WithFullPath,
        (tableState, levelIndex) =>
            Table.AddColumn(
                tableState,
                "Level" & Text.From(levelIndex + 1),
                each if List.Count([PathList]) > levelIndex
                     then [PathList]{levelIndex}
                     else null,
                type nullable text
            )
    ),

    FinalTable = Table.RemoveColumns(LevelAdded, {"PathList"})
in
    FinalTable

The List.Accumulate pattern here iterates over level indices 0 through MaxDepth, and for each one adds a column to the table. Each iteration passes the updated table as the accumulator into the next iteration. This technique is covered in detail in Advanced M: Iterators, Accumulators, and Recursive Patterns.

---

Protecting Against Circular References

Real-world data is messy. An improperly maintained hierarchy might have Employee A reporting to Employee B while Employee B reports to Employee A — a circular reference that would cause your recursive function to loop forever.

You can add a depth guard: pass a counter through the recursion, and bail out if it exceeds a reasonable maximum:

    GetAncestorPathSafe = (id as nullable number, optional visited as list) as list =>
        let
            VisitedList = if visited = null then {} else visited,
            CurrentEmp  = FindEmployee(id),
            HasParent   = CurrentEmp <> null and CurrentEmp[ManagerID] <> null,
            AlreadySeen = List.Contains(VisitedList, id)
        in
            if CurrentEmp = null or AlreadySeen then {}
            else if not HasParent then { CurrentEmp[Name] }
            else GetAncestorPathSafe(
                    CurrentEmp[ManagerID],
                    VisitedList & { id }
                 ) & { CurrentEmp[Name] }

The visited list tracks every node ID encountered during a single path traversal. If we encounter an ID we've already seen, we know we're in a loop and return an empty list instead of recursing further. The optional keyword makes the first call clean — you just write GetAncestorPathSafe(8) without passing an empty list manually. This kind of optional parameter pattern is explained in Writing Custom M Functions in Power Query.

Warning: The visited-list approach adds overhead because each recursive call builds a new list. For deeply nested trees with thousands of nodes, this will slow down noticeably. Use it when your data source is untrusted or user-maintained. For clean, validated hierarchies, you can omit it for better performance.

---

Hands-On Exercise

Set up a blank Power Query query and implement the following using the techniques from this lesson:

Scenario: You work for an e-commerce company. Your product catalog is stored as a parent-child category tree:

CategoryID	CategoryName	ParentCategoryID
1	All Products	null
2	Electronics	1
3	Clothing	1
4	Laptops	2
5	Smartphones	2
6	Men's Clothing	3
7	Women's Clothing	3
8	Gaming Laptops	4
9	Ultrabooks	4
10	Winter Jackets	6

Tasks:

1. Build the source table in Power Query using Table.FromRows.
2. Write a GetCategoryPath recursive function that returns the full breadcrumb path for any CategoryID.
3. Add a FullPath column to the table using Text.Combine with > as the separator.
4. Add a Depth column where All Products = depth 0.
5. Add a ParentCategoryName column that shows just the immediate parent's name (or "(Root)" if there is no parent).
6. Bonus: Add circular reference protection using a visited list.

---

Common Mistakes & Troubleshooting

"Expression.Error: We cannot apply field access to the type Null" This happens when FindEmployee returns null and you try to access a field on it. Make sure every branch of your recursive function checks for null before accessing record fields. The pattern if CurrentEmployee = null then {} else ... is your first guard.

"The query seems to run forever / Power Query stops responding" You've almost certainly hit a circular reference in the data, or you're calling the recursive function without proper base case guards. Add Table.Buffer to your source, verify your base case (ManagerID = null for roots), and add a visited-list guard as shown above.

"The FullPath for root nodes shows just their own name but the Depth is -1" This means your path list is empty for root nodes. Check that your base case returns { CurrentEmployee[Name] } (a single-element list), not {}. Depth is computed as List.Count(PathList) - 1, so a root with a one-element path gets depth 0 correctly.

"Level columns are all null for deep nodes" Double-check your index arithmetic. {AncestorPathList}{0} is the first element (root), and {AncestorPathList}{levelIndex} uses zero-based indexing. If your Level1 column is supposed to be the root, you want index 0, not index 1.

Performance is very slow on large tables Hierarchical recursion in M is inherently O(n × depth) in time complexity. The two most important optimizations are: (1) Table.Buffer the source before converting to records, and (2) avoid calling Table.ToRecords inside the recursive function itself. If performance is still unacceptable on very large datasets, consider pre-flattening the hierarchy in SQL before loading into Power Query, or using Power BI's native parent-child DAX functions for the reporting layer.

Note: If your data originates from a relational database that supports recursive CTEs (SQL Server, PostgreSQL, etc.), you may be able to push the hierarchy resolution to the database using a custom native query. This avoids bringing all the raw data into M at all. See Implementing Custom Query Folding Logic in M: Keeping Transformations Native to the Data Source for how to approach that.

---

Summary & Next Steps

You've built a complete hierarchical flattening pipeline from scratch. Here's what you implemented:

• A FindEmployee helper that looks up any node by ID from a buffered list of records
• A GetAncestorPath recursive function that walks from any node to the root and returns a list of names
• Applied that function across every row with Table.AddColumn to produce breadcrumb paths and depth levels
• Used List.Accumulate to dynamically generate level columns without hardcoding the depth
• Added circular reference protection using a visited list passed through the recursion

These patterns extend beyond org charts. The same approach works for product category trees, account hierarchies in accounting systems, folder/document trees, and geographic rollups. Anywhere you have a self-referencing parent-child table, you now have the tools to flatten it.

Where to go next:

• If you want to make your hierarchy functions reusable across multiple queries in the same Power BI file, look at Building a Reusable Function Library in Power Query — you'll learn how to package logic as named function queries.
• If your hierarchy data comes from a JSON or XML API (which is common for systems like SharePoint and SAP), Advanced JSON and XML Processing in Power Query M Language will show you how to navigate nested structures before applying the flattening techniques from this lesson.
• To understand how M evaluates the recursive calls and why buffering matters at each step, revisit the Advanced M: Iterators, Accumulators, and Recursive Patterns lesson.