Power Query Problems

How to Recursively Follow Parent-Child Materials in Power Query

Muhammad Talha Sep 30, 2026 869 views

Question details

The user needs a dynamic, maintainable way to recursively follow and expand parent-child material relationships (such as a Bill of Materials) in Power Query without creating excessive manual merge steps.

How to Recursively Expand Parent-Child Hierarchies in Power Query

Product: Microsoft Excel (Power Query)
Device & OS: not provided
Scenario: Organizing and expanding product, assembly, and component relationships iteratively to build a complete hierarchy for reporting or charting.
Observed behavior: Manually creating multiple merge steps to link parent and child data is tedious, static, and difficult to maintain when dealing with deeply nested component layers.

Before you start

Ensure your source data is organized into a clean staging table with distinct 'Parent ID' and 'Child ID' columns before attempting to build a recursive hierarchy.

Solution 1Recommended

Use a Custom M Function to Recursively Expand the Hierarchy

Create a custom Power Query function that repeatedly merges the child rows back onto the source table until the lowest component level is reached.

Instead of manually joining tables for every generation of a product assembly, you can use the Advanced Editor to write a custom M query. This function iterates through the dataset dynamically, finding components of components until no further children exist.

Using a staging table stores each generation systematically, which can later be flattened and loaded into a PivotTable or Data Model.

Load your staging table

Select your parent-child data in Excel, go to the 'Data' tab, and click 'From Table/Range' to load the dataset into the Power Query Editor. Name this query 'BOM_Data'.

Create a blank query

Right-click in the Queries pane on the left, select 'New Query', and then choose 'Blank Query'. This will hold your custom recursive function.

Write the recursive M code

Click 'Advanced Editor' on the Home tab. Write an M query that takes a parameter (like the current parent level) and uses 'Table.NestedJoin' to merge it against the 'BOM_Data' query, calling itself if the resulting child table is not empty.

Invoke the function

Apply the new custom function to your top-level parent items by going to 'Add Column' > 'Invoke Custom Function'. Select your newly created recursive function and map it to the Parent ID column.

Expand and load

Click the expand icon (two diverging arrows) on the newly generated column to display the nested child records. Continue expanding until no more nested tables appear, then click 'Close & Load' to return the hierarchy to Excel.

Use a Custom M Function to Recursively Expand the Hierarchy

Data Visualization: Once the flattened hierarchy is loaded into Excel 365, you can add it to the Data Model (Power Pivot) to create structured relationship charts or multi-level pivot tables.

Free Microsoft Office alternative

Looking for a Lightweight, Everyday Spreadsheet Solution?

While highly complex recursive M code requires Microsoft Excel's Power Query environment, WPS Spreadsheets offers a lightning-fast, highly compatible, and free alternative for standard data analysis, reporting, and daily spreadsheet management.

1. Download the software: Visit the official WPS Office website and click 'Free Download' to get the installer.
2. Install WPS Office: Run the downloaded setup file and follow the quick on-screen prompts to complete the installation.
3. Open your data: Launch WPS Spreadsheets and open your existing .xlsx files directly to begin analyzing your data with ease.

Seamlessly opens, edits, and saves standard Microsoft Excel formats (.xlsx, .xls, .csv).Built-in advanced formulas, pivot tables, and intuitive charting tools for BOM summaries.Extremely lightweight design ensuring fast startup times even on older devices.Free to use with a familiar user interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is manual merging bad for parent-child hierarchies?

Manual merging creates a fixed, static number of levels. If a new product adds a deeper component layer to your Bill of Materials (BOM), the manual query will fail to capture it unless you specifically go in and add another merge step.

Does Power Query have a recursion limit?

While Power Query does not enforce a strict hard limit on recursion depth, highly complex recursive functions on massive datasets can hit memory limits or timeout errors. Always ensure your source data does not contain circular references (e.g., a parent that is a child of its own child) to prevent infinite loops.

Can I visualize this expanded hierarchy in Excel?

Yes. Once the data is iteratively flattened into a final table by Power Query, you can load it into the Data Model (Power Pivot) and use PivotCharts, or external add-ins, to map out the assembly process generation by generation.

Excel & Data Problems