Hi there Alteryx community,
I hope you can help me: I have a multi-level Bill of Materials, with the main products, components, sub-components, sub-sub-components etc. listed. There can be many 'levels' in the chain of the product bill of material. Here is an example dataset:
| Product | Material |
| Product 1 | Component 1 |
| Product 1 | Component 2 |
| Product 1 | Component 3 |
| Component 1 | Subcomponent 1 |
| Component 1 | Subcomponent 2 |
| Subcomponent 2 | Subsubcomponent 3 |
| Product 2 | Component 4 |
| Subsubcomponent 3 | Subsubsubcomponent 4 |
I want to transform this table into a table where I can see for each main product (defined as: products at the beginning of the 'chain') what are all of the materials for each product. The output for this example should look something like this:
| Product | Material |
| Product 1 | Component 1 |
| Product 1 | Subcomponent 1 |
| Product 1 | Subcomponent 2 |
| Product 1 | Subsubcomponent 3 |
| Product 1 | Subsubsubcomponent 4 |
| Product 1 | Component 2 |
| Product 1 | Component 3 |
| Product 2 | Component 4 |
I suspect that potentially recursion would need to be used (as for each product we don't know up front how many 'levels' it would have), but I'm really banging my head trying to figure out how to achieve this.
Hoping that there will be some good suggestions from the community :)
