Bill of Materials (BOM) solution in Excel and PowerBI

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:

  1. Explosion

  2. 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:

txt-file for download: BOM_Code.txt

The query “Invoked Function” invokes the function and needs the following parameters adjusted to your BOM-table:

  1. “Table”: Name of the query that holds your BOM-table
  2. “Parent”: Name of the column that holds the (parent) product ID or name
  3. “Child”: Name of the column that holds the (child) component ID or name
  4. “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:   BoM-Table4_adj.xlsx

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 😉

Comments (13) Write a comment

  1. As I’ve no SQL Server on my computer, therefore in addition to the BOM table you’ve already provided in the file BoM-TableDAX4.xlsx, could you do me one more favor by sharing an Excel version of Adventure Works 2008-database? I would appreciate it very much.


  2. I was told that “First you use Power Query (M) to query data sources, clean and load data. Then you use DAX to analyze the data in Power Pivot. Finally, you build pivot tables (Excel) or data visualisations with Power BI.” However, I’ve been learning from you that M has the capability to do advanced calculations like DAX does. Thanks.


    • M has a much more versatile language repository than DAX (recursion for example). Problem is that the algorithm (especially in Excel) starts to kill performance without giving the user the chance to monitor or manage it. This makes performance tuning often a very time consuming and frustrating job.

      DAX is THE language if you want to produce reports from datamodels with different tables. Filter propagation across multiple tables only works with DAX. So if you would want to aggregate any of the results on a month- or year-basis for example, you would have to create those additional columns in the table with M, whereas with DAX you could use one calendar-table and use its aggregated attributes on all tables of your model.

      Also the VertiPaq-engine that DAX runs on is WAY faster for large datasets than M.


  3. Thanks a lot for your deeper explanation about the features and pros and cons of each languages.


  4. But once you have expanded the BOM, why you don’t create a regular hierarchy? Is it because it would fail the calculation of aggregated levels?


    • Hi Marco,
      Thank you for taking interest in this solution.

      I didn’t create regular hierarchies because this solution wouldn’t be dynamic then any more (one needs to adjust the number of levels manually) and because it isn’t necessary from a logical point of view (but of course, when performance comes into play, that additional calculation might add). The idea behind this concept was to create a “simple stupid”-solution that requires minimal additional handling.

      I need to answer your question/assumption if/that the calculation of aggregated values would fail with your own words: “It depends…” 😉
      … on whether you want to use the
      1) intelligent hierarchy-measures that you are used to (where you calculate the effect that the quantities of the previous levels have on the current one) –> then you have to reference the old “PerAssemblyQty” from the original table (it’s still in there) or
      2) you want to reference the new “TotalQty”-column. Then you have to use a “Simple-stupid”-measure: A single SUM (just like in the EasyP&L in my previous blogposts), as the effects of the previous levels is already included there.

      Hope this makes sense?


  5. Can’t seem to get this to work. Complains that can’t see tblBOM as table on invoke.


    • If you want to apply it to your own data, make sure that it’s formatted as table and that table has the name tblBOM:
      Convert range to table


    • OK. In this case you have to remove the quotation marks around “tblBOM”. (It’s a string now instead of a reference to a query)


      • Have uploaded a new file that makes this step obsolete. Now, when calling the function, the dialogue will prompt you to select a query. No need to adjust anything then.


Leave a Reply