Handling multilevel bill of materials (BOM) without VBA in Excel and PowerBI is now a piece of cake: Just pass 4 parameters into the M-function below and it will return a table that holds everything you need for:
Order list (“total quantities”)
3. Implosion (“where used”): Will be covered in next blogpost
The format of the input-data for this function needs to be like the example used from the Adventure Works 2008-database, where all products on the top of the hierarchy also have an entry in the child-column (the components), leaving the parent column blank:
BOM-code beautified with Lars Schreiber’s M-editor: goo.gl/KW4p8Q
txt-file for download:
The query “Invoked Function” invokes the function and needs the following parameters adjusted to your BOM-table:
- “Table”: Name of the query that holds your BOM-table
- “Parent”: Name of the column that holds the (parent) product ID or name
- “Child”: Name of the column that holds the (child) component ID or name
- “Qty”: Name of the column that holds the quantity per produced item
This query will be loaded to the datamodel and there I’ve added some DAX-PATH-columns that might come in handy for some cases.
New to M?
Watch this video where I show how to use the function code with your own data:
Details for techies and M-code-fans:
This technique is MUCH faster than the PC-solution I’ve posted here! (…just don’t ask me why & be prepared for significant performance drop offs once you try to modify anything…) It can also return the path for children with multiple parents, so an excellent workaround for this missing functionality of the DAX PATH-function (check datamodel in file). All other PATHx-functions will work, so just take the PATH from M. (Also the dynamic creating of multiple columns from the post above still works fine)
Noticed the clean code in step “AddFields”? M can look like a serious programming language once you strip off the elements that makes it a live programming language 😉
Subscribers can download the file with sample data and the pivots shown above:
Stay tuned until next week when I will post the pattern for the BOM-implosion (“where used”)
Edit 2017-May-14: Performance of this solution in Excel can decrease rapidly with larger dataset while it runs good in Power BI Desktop. Read details and workaround here.
Enjoy & stay queryious 😉