Forum Discussion
Single level Bill of Material pulled into Excel Power Query - How can I make Multi level BOM Table
I have a data pull from my erp system that comes through the odbc connectivity through power query into an excel workbook for all of our bill of materials. It is a single level pull. I need to figure out how to take this data with all the boms and build multi level bom table showing parent/child/grandchild/great grandchild bom relationships. I need this so that I have a full where used. If I make a change to the cost of a raw material in a bulk bom, I have to roll the cost of that bulk bom as well as every other bom that consumes that bulk and on through to the end of the dependent boms. Our boms are changing constantly and we are adding new dependent boms all the time, so I can't just do this once and use that. I have to repull all the boms from the system into power query so that I have the most up to date boms.
Any suggestions would be appreciated. Our erp system does not have such a report and pulling these one level at a time through all the relationships takes a long time. I am having to do these cost updates every week now. I'm losing hours at a time on this and would love to be able to pull the single level boms in and have it show me the mult level boms in a pivot table or another form.
Thank you very much for your time.
- On my github I have a bill of material recursive power query function that allows unlimited levels.
Would it solve your issue?
https://github.com/edouardbrasier/Recursion-example-bill-of-materials I used your problem as a test of the applicability of Lambda function.
Unfortunately I may have gone in the wrong direction, starting with a database parent-child table and breaking it down to a single list of quantities.
SergeiBaklan Since the main relationships are recursive in nature, I wondered whether functional programming and Lambdas may work better than VBA and an imperative programming. At the moment debugging is hard work!
PeterBartholomew1 Thank you for the quick response. As soon as I get a chance I will work through this to see what I can manage with it. I appreciate your help.
- SergeiBaklan Thank you for the quick response. As soon as I get a chance I will work through this to see what I can manage with it. I appreciate your help.
