let
    Source = BOMReport,
    FilterOnlyPurchaseItems = Table.SelectRows(Source, each ([PurchaseItem] <> null)),
    Cleanup = Table.SelectColumns(FilterOnlyPurchaseItems,{"Component", "PathItems", "PathExplode", "TopParentProduct", "PurchaseItem", "Index"}),

	// Skip the last element of the list because it's the purchase item itself and skip the first element of the list because it sits in "TopParentProduct" already
    SelectOnlyIntermediates = Table.AddColumn(Cleanup, "Intermediates", each List.RemoveFirstN(List.RemoveLastN([PathItems],1),1)),

	// Expanding the resulting list will create one row per intermediate product ("Where used")
    ExpandIIntermediates = Table.ExpandListColumn(SelectOnlyIntermediates, "Intermediates")
in
    ExpandIIntermediates