Merge Query To Itself In Loop

[alindquist]

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:

PartNum	MtlPartNum
Assembly1	ComponentA
Assembly1	Assembly2
Assembly1	ComponentB
Assembly2	Assembly3
Assembly2	ComponentC
Assembly3	ComponentD

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:

Assembly1

-- ComponentA

-- ComponentB

-- Assembly2

---- ComponentC

---- Assembly3

------ComponentD

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?

[lbendlin]

You don't need any of that. Use the PATH* functions in DAX for your hierarchy work.

PATH function (DAX) - DAX | Microsoft Docs

[alindquist]

@lbendlin if I use the sample set of data from my initial post and try adding a new column using the PATH function, it gives me the following error:

[lbendlin]

That is correct - all referenced parents must exist in the original client column (their parent can be blank).  That's something you will want to correct in your source data as you need that later.