cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alindquist
Helper II
Helper II

Merge Query To Itself In Loop

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:

 

PartNumMtlPartNum
Assembly1ComponentA
Assembly1Assembly2
Assembly1ComponentB
Assembly2Assembly3
Assembly2ComponentC
Assembly3ComponentD

 

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:

 

alindquist_0-1636738657480.png

Then if I expand MtlPartNum, it would result in this:

alindquist_1-1636738714406.png

 

I can then do another merge on the new MtlPartNum.1 column, like this:

 

alindquist_2-1636738751325.png

Then expand the MtlPartNum field again as MtlPartNum.2

alindquist_3-1636738804518.png

 

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?

3 REPLIES 3
lbendlin
Super User
Super User

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

 

PATH function (DAX) - DAX | Microsoft Docs

@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:

alindquist_0-1636985771592.png

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.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors