Forum Discussion

BYonRo04's avatar
BYonRo04
Regular Visitor
8 months ago
Solved

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...
Need Help
  • Zanqueta's avatar
    Zanqueta
    8 months ago

    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:
    1. Load your BOM table into Power Query.
    2. Use Merge Queries or Self-Join:
      • Merge the BOM table to itself on Component = Material to bring the next level.
    3. Repeat the merge for up to 4 levels (since your hierarchy depth is known).
    4. Expand all merged columns so you have:
      Finished Product | Raw Material | Description
    5. 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 🌀.

  • Hans-Georg_Puls's avatar
    Hans-Georg_Puls
    8 months ago

    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.

Zanqueta's avatar
Zanqueta
Super User
8 months ago

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:
  1. Load your BOM table into Power Query.
  2. Use Merge Queries or Self-Join:
    • Merge the BOM table to itself on Component = Material to bring the next level.
  3. Repeat the merge for up to 4 levels (since your hierarchy depth is known).
  4. Expand all merged columns so you have:
    Finished Product | Raw Material | Description
  5. 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 🌀.