I have a set of data with 2 columns, a part number, and a list of materials used to assemble that part. A part could be made of a material that also has its own set of materials, and it could go down an indeterminant set of levels. Here is a sample set of data:
In this set of data, Assembly1 is made up of ComponentA, ComponentB, and Assembly2. Assembly2 also exists in the PartNum column with its own set of components, ComponentC and Assembly3. And Assembly3 has its own component, ComponentD.
So the hierarchy of the dataset is:
I am trying to flatten out this hierarhcy. I could do this manually by loading that table of data, the merge with itself on MtlPartNum and PartNum, like this:
Then if I expand MtlPartNum, it would result in this:
I can then do another merge on the new MtlPartNum.1 column, like this:
Then expand the MtlPartNum field again as MtlPartNum.2
I can keep doing this until all of the rows are null, meaning there are no more sub-components. However, each part in my database could go down a different set of levels. The end goal is to be able to select a part (e.g. Assembly1) and see all of the levels beneath that part.
Is there a way to create a looping script that would perform those merging steps to create a new column for each level in the hierarchy?