I'm tying to display a multi-level BOM within a pivot table

[mkittell]

I am trying to transform my data into a more readable format. I am pulling the data from a SQL database through power query. From there, I'm looking to display this data in a hierarchal format with a pivot table. I have been trying various combinations of data relationships and have been unsuccessful in producing my desired result.

The goal of this is to cost data in from this database, and roll up the cost from sub components into their parent "machines" to show a summary of the costs at a project, or machine level.

For now, I'm simply trying to develop the hierarchy from a paired down data table referencing a single project and a few machines. See attached images for reference.

The desired effect for this pivot table is to show first a list of the projects in the rows. When I expand a given project, I would like to see the "top level" machines related to that project first. Each of these machines can contain child machines, products or both. When I expand a machine, I would like to then see any child machines and any products. These child machines could contain their own child machines.

In the example attached, the data almost shows up as intended but I cannot figure out how to nest the child machines within the parent machines while also showing some products under those. The desired effect would be as follows (I've added a P for project & M for machine on the ID numbers for readability):

P2105:
-M808
-M915
-5501
-5823
-5824
-8017
-8019
-8020
-109
-110
-366
-803
-5791
-5792
-6675
-6676
-6822
-6930
-6931
-M1112
-M1113

It is worth noting that I am not certain that a pivot table is the best way to achieve my desired result. Any other methods to achieve this result are welcome. Thank you in advance, this problem has been driving me nuts!

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[mkittell]

Thank you, I have included some sample data. I have removed the link to the query database. This is not exactly as it's coming in from the server but should serve the purpose of my question. Let me know if any other information would help.