Forum Discussion
Best way to create Indented Bill Of Material
I need to create a workbook where I can enter a Part Number into a cell, data will be retrieved from a SQL database and processed according to the Part Number entered it to create an indented bill of material. I'm looking for ideas about the best way to do this.
My DB table contains 2 columns (there are others, but they're not important to the task), mainPN and subPN. The tricky part is that the bill of material can have infinite levels and I'm guessing some kind of loop is required. The Part Number entered by the user would be found in mainPN, but could have multiple entries. Each mainPN would have a different subPN and in turn the subPN value could be a mainPN value with its own subPN value if there are components to those part numbers. I've attached an example file. I also need to populate a levels showing the hierarchy by indenting the number.
I'm able to connect to the DB with Get Data and retrieve the table with Power Query, but I'm stuck after that. I have no experience with Power Query. I'm guessing a little VBA is needed for most of this and also a button tied to the VBA macro to execute it.
Really appreciate all your help!
1 Reply
If you can define a maximum number of levels, you could set-up a fairly simple query that merges itself a number of time with itself, as demonstrated in the attached workbook.
With an infinite number of levels of sub parts, though, you'll need something better, but that will not be very straight-forward. See link below.
https://techcommunity.microsoft.com/t5/excel/parent-child-flattening-multiple-levels/m-p/2744756
