Iterative Parts List - How to generate useful hierarchies

[Anonymous]

Dear Power BI Community

I am trying to solve the following problem.

There are the following tables:

• Item_List - which contains all items of the company
• Parts_List - Contains the information of which parts an item consists of.

I would like to have hierarchies of the parts list at the end in reports. That means: For each article (Item_List), e.g. show in a matrix which individual parts are installed.

The problem is that the Parts_List is iterative. It consists of only 2 columns:

• Parent_ID: Upper level, StartID from Item_List
• Child_ID: Next lower level or installed individual part in the Item

The Child_ID must then be searched for again in the Parent_ID and then you get the next level with the Child-ID on the new row.

How can you solve the problem in Power Query so that you can then evaluate the parts list in reports and have a hierarchy with Item, Part1, Part2, Part3, Part5...?

Here is a WeTransfer Link of the Dataset: https://we.tl/t-fKLINa1EXj

I hope I could explain the problem well and I am looking forward to the solutions. Many thanks in advance.

[BA_Pete]

Hi @Anonymous ,

This is potentially a fairly in-depth process depending on your specific implementation/use case.

If you want to do this in DAX, then I would suggest looking into the PATH function family:

https://docs.microsoft.com/en-us/dax/parent-and-child-functions-dax 

If you want to do this in Power Query, then I would suggest looking into functional transformation:

https://pivotalbi.com/dynamically-flatten-a-parent-child-hierarchy-using-power-query-m/ 

Pete

[Anonymous]

Thank you very much for your answer and help.

And please excuse the late response, the project has been put on hold.

First a word about your second solution if I want to solve it with Power Query: The solution would be great, but due to the large amount of data it exceeds the calculation time and the update of the data set in the online service stops after about 2 hours of loading time.

For this reason I am now trying your first suggestion using DAX, the problem is that in the last iteration the Child ID can no longer be found in the Parent ID Column. The formula "Path" gives an error in this case.

Do you have any tips on how I can fix this problem or what I am doing wrong?

Thank you very much!

Here is another demo data set: https://we.tl/t-OXffAXhL3R