Exploding Intermediates into Raw Materials

Hello everyone,

I would really appreciate any help with the following as I have been struggling with this for few days with no success.

My dataset represents the bill of materials used to produce finished products. Every finished product is composed of intermediates and raw materials. All intermediates are also composed of raw materials.

I want to create a workflow that will list the bill of materials of every finished product by exploding the intermediates into raw materials.

Sample Input

| Column 1 | Column 2              | Column 3  | Column 4    | E    | F            |
| Product  | Type                  | Component | Description | Type | BOM Quantity |
| A        | Finished Product Part | B         | Material-B  | HALB | 1            |
| A        | Finished Product Part | C         | Material-C  | HALB | 2            |
| A        | Finished Product Part | D         | Material-D  | ROH  | 3            |
| A        | Finished Product Part | E         | Material-E  | ROH  | 3            |
| A        | Finished Product Part | F         | Material-F  | ROH  | 4            |
| B        | Intemediate           | C         | Material-C  | HALB | 5            |
| B        | Intemediate           | K         | Material-K  | ROH  | 6            |
| C        | Intemediate           | L         | Material-L  | ROH  | 1            |
| C        | Intemediate           | M         | Material-M  | ROH  | 1            |

Sample Output

| Column 1                  | Column 2                                                         | Column 3  | Column 4    | E    | F            |
| Product                   | Type                                                             | Component | Description | Type | BOM Quantity |
| A                         | Finished Product Part                                            | D         | Material-D  | ROH  | 3            |
| A                         | Finished Product Part                                            | E         | Material-E  | ROH  | 3            |
| A                         | Finished Product Part                                            | F         | Material-F  | ROH  | 4            |
| A                         | Finished Product Part                                            | K         | Material-K  | ROH  | 6            |
| A                         | Finished Product Part                                            | L         | Material-L  | ROH  | 7            |
| A                         | Finished Product Part                                            | M         | Material-M  | ROH  | 7            |
| [Sample Input-Output.xlsx | attachment](upload://pDhQEaLfYsKCyWHxuNiVt81v9Rk.xlsx) |           |             |      |              |

Thanks a lot