Forum Discussion
BOM tree
I am looking for a solution for the following situation:
I have a BOM. This BOM contains a semi-finished product and some raw materials.
But this semi-finished product in turn contains another semi-finished product and raw materials.
The situation repeats up to level four.
My question is:
How can I summarize all the materials in the finished product in a single report?
| FG | |||
| Material | Material Description | Component | Material Description_Component |
| 100000 | FINISHED Product | 900100 | Raw material 1 |
| 100000 | FINISHED Product | 900101 | Raw material 2 |
| 100000 | FINISHED Product | 200000 | SF1 |
| SF1 | |||
| Material | Material Description | Component | Material Description_Component |
| 200000 | Semifinished Product 1 | 900102 | Raw material 3 |
| 200000 | Semifinished Product 1 | 300000 | SF2 |
| SF2 | |||
| Material | Material Description | Component | Material Description_Component |
| 300000 | Semifinished Product 2 | 900103 | Raw material 4 |
| 300000 | Semifinished Product 2 | 400000 | SF3 |
| SF3 | |||
| Material | Material Description | Component | Material Description_Component |
| 400000 | Semifinished Product 3 | 900104 | Raw material 5 |
| 400000 | Semifinished Product 3 | 400000 | SF4 |
| SF4 | |||
| Material | Material Description | Component | Material Description_Component |
| 500000 | Semifinished Product 4 | 900105 | Raw material 6 |
| 500000 | Semifinished Product 4 | 900106 | Raw material 7 |
| REPORT | |||
| Material | Material Description | Component | Material Description_Component |
| 100000 | FINISHED Product | 900100 | Raw material 1 |
| 100000 | FINISHED Product | 900101 | Raw material 2 |
| 100000 | FINISHED Product | 900102 | Raw material 3 |
| 100000 | FINISHED Product | 900103 | Raw material 4 |
| 100000 | FINISHED Product | 900104 | Raw material 5 |
| 100000 | FINISHED Product | 900105 | Raw material 6 |
| 100000 | FINISHED Product | 900106 | Raw material 7 |
| 100000 | FINISHED Product | 200000 | SF1 |
| 100000 | FINISHED Product | 300000 | SF2 |
| 100000 | FINISHED Product | 400000 | SF3 |
| 100000 | FINISHED Product | 500000 | SF4 |
Thanks
Hi BYonRo04 seems you are describing is a multi-level Bill of Materials (BOM) explosion where you need to flatten all components (including raw materials and semi-finished products) into a single view for the finished product. This is a classic hierarchical data problem, and Power BI can handle it well using either Power Query or DAX with PATH functions.
✅ Recommended Approach: Power Query (Best for Performance)
Power Query is ideal because it can recursively expand the hierarchy before loading into the model.Steps:- Load your BOM table into Power Query.
- Use Merge Queries or Self-Join:
- Merge the BOM table to itself on Component = Material to bring the next level.
- Repeat the merge for up to 4 levels (since your hierarchy depth is known).
- Expand all merged columns so you have:
Finished Product | Raw Material | Description
- Remove nulls and keep only raw materials for the final flattened view.
This creates a single table with all components linked to the finished product.✅ Alternative Approach: DAX (If You Need Dynamic Explosion)
If you want this dynamic in the report (not pre-flattened), you can use PATH and PATHITEM functions, but they require a parent-child structure.Steps:- Create a calculated column for Parent (Material) and Child (Component).
- Use PATH(Material, Component) to build the hierarchy.
- Then use PATHITEM in a measure or calculated table to extract all levels.
Example for a calculated table:FlattenedBOM = GENERATE( FILTER(BOM, BOM[Material] = "100000"), // Finished product ADDCOLUMNS( PATHITEM(BOM[HierarchyPath], [Index], TEXT), "Component", PATHITEM(BOM[HierarchyPath], [Index], TEXT) )But this approach is more complex and less performant for large BOMs.✅ Why Power Query is Better
- Handles recursion easily when depth is known.
- Reduces complexity in the data model.
- Improves report performance because the hierarchy is resolved before loading.
If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
Hi BYonRo04 , if your requirement is to get the REPORT table shown in your posting there is even an easier approach. Just append all SF tables and do some replacements. Here is the M Code:
let
Source = FG,
#"Appended Query" = Table.Combine({Source, SF1, SF2, SF3, SF4}),
#"Replaced Value" = Table.ReplaceValue(#"Appended Query", each [Material Description], if true then "FINISHED Product" else "FINISHED Product", Replacer.ReplaceText,{"Material Description"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value", each [Material], if true then "100000" else "100000",Replacer.ReplaceText,{"Material"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Product"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Material Description_Component", Order.Ascending}})
in
#"Sorted Rows"Hope that helps.
4 Replies
Hi BYonRo04 seems you are describing is a multi-level Bill of Materials (BOM) explosion where you need to flatten all components (including raw materials and semi-finished products) into a single view for the finished product. This is a classic hierarchical data problem, and Power BI can handle it well using either Power Query or DAX with PATH functions.
✅ Recommended Approach: Power Query (Best for Performance)
Power Query is ideal because it can recursively expand the hierarchy before loading into the model.Steps:- Load your BOM table into Power Query.
- Use Merge Queries or Self-Join:
- Merge the BOM table to itself on Component = Material to bring the next level.
- Repeat the merge for up to 4 levels (since your hierarchy depth is known).
- Expand all merged columns so you have:
Finished Product | Raw Material | Description
- Remove nulls and keep only raw materials for the final flattened view.
This creates a single table with all components linked to the finished product.✅ Alternative Approach: DAX (If You Need Dynamic Explosion)
If you want this dynamic in the report (not pre-flattened), you can use PATH and PATHITEM functions, but they require a parent-child structure.Steps:- Create a calculated column for Parent (Material) and Child (Component).
- Use PATH(Material, Component) to build the hierarchy.
- Then use PATHITEM in a measure or calculated table to extract all levels.
Example for a calculated table:FlattenedBOM = GENERATE( FILTER(BOM, BOM[Material] = "100000"), // Finished product ADDCOLUMNS( PATHITEM(BOM[HierarchyPath], [Index], TEXT), "Component", PATHITEM(BOM[HierarchyPath], [Index], TEXT) )But this approach is more complex and less performant for large BOMs.✅ Why Power Query is Better
- Handles recursion easily when depth is known.
- Reduces complexity in the data model.
- Improves report performance because the hierarchy is resolved before loading.
If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
I adopted the first solution (Power Query).
It was very simple, even if it required quite a lot of system memory resources.
This is because the database has over fifty thousand rows.Thanks for the help, Zanqueta.
Hi BYonRo04 , Thanks for the update and the information regarding the solution. We appreciate it and are happy to know your issue is solved. If you have any other queries, please feel free to create a new post, we are always happy to help.
Thank you Zanqueta for your continued contribution in the community.
Hi BYonRo04 , if your requirement is to get the REPORT table shown in your posting there is even an easier approach. Just append all SF tables and do some replacements. Here is the M Code:
let
Source = FG,
#"Appended Query" = Table.Combine({Source, SF1, SF2, SF3, SF4}),
#"Replaced Value" = Table.ReplaceValue(#"Appended Query", each [Material Description], if true then "FINISHED Product" else "FINISHED Product", Replacer.ReplaceText,{"Material Description"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value", each [Material], if true then "100000" else "100000",Replacer.ReplaceText,{"Material"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Product"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Material Description_Component", Order.Ascending}})
in
#"Sorted Rows"Hope that helps.
