Bill of Materials Implosion or “Where used Analysis” in Excel and Power BI

This is the last part of my little series about bill-of-materials (BOM) calculations made easy with M in Power BI and Power Query: The implosion that shows where each part is used:

BOM: “Where used?”

 

When we look closely at the table “BOMReport” from the previous post, we see that the data is already there: “PathExplode” shows that the component “HL Spindle/Axis” (1) is used in the TopParentProduct “Road 150 Red, 62” (2) via some intermediate products (3):

 

And if we filter on the example from the pivot-table from above, we can spot that pattern as well:

All we have to do is to create one row for each “Where-used” item. But as we have columns for the compenent itself already as well as for the TopParentProduct, we actually only want to create additional rows for the intermediate products.

Therefore we could split the column “PathExplode” up into its components. But that’s not necessary because it has been generated from the lists in column “PathItem”. So we just have to eliminate the first and the last item from those lists and expand them:

M-Code to extract the intermediate parts:

M-Code as text: BOM_ImplosionCalc1.txt

Subscribers can download the Excel-file:   BOM_ImplosionCalc1.xlsx

Enjoy & stay queryious 🙂