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-Function

BOM-code beautified with Lars Schreiber’s M-editor: goo.gl/KW4p8Q

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

Check out how to calculate the totals costs for the components here and stay tuned for 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 (28) 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.

    Reply

  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.

    Reply

    • 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.

      Reply

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

    Reply

  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?

    Reply

    • 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?

      Reply

      • The follow-up-postfollow-up-post will probably make this more clear. I guess that you were referring to the upwards-aggregation of the total quantities (“CountParts”). Actually, the measure needs a filter on purchase items only, if using it without a bridge-table:
        CountPartsH :=
        CALCULATE ( SUM ( BOMReport[TotalQty] ), BOMReport[PurchaseItem] <> BLANK () )

        Reply

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

    Reply

    • 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

      Reply

    • 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)

      Reply

      • 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.

        Reply

  6. Hi, tried this in both Excel and Power BI but cannot get it to work. I get an error message saying:

    —-
    An error occurred in the ‘’ query. Expression.Error: The column ‘Column1’ of the table wasn’t found.
    Details:
    Column1
    —-

    any help would be appreciated

    Reply

    • Are you using a local version? Then you might need to replace “Column” by the expression for new columns in your language.
      Sorry, didn’t consider that.

      Reply

      • Imke thank you for this post! Could you please explain a little more about the column1 error, I am a noob and I can’t find anything while searching for new column expression in US version. I appreciate your help!

        Reply

        • Hi Kevin,
          have downloaded both (full file and M-code) and both work for me. Which one are you using?
          Please debug by replacing the first 3 lines of code with the following:

          //(Table as table, Parent as text, Child as text, Qty as text) =>

          let

          Table = ..YourTableName.. ,
          Parent = ..ParentColumnName.. ,
          Child = ..ChildColumnName.. ,
          Qty = ..QtyColumnName..,

          ChgTypeKeyCols = Table.Buffer(Table.TransformColumnTypes(Table,{{Parent, type text}, {Child, type text}})),

          And fill in the parameter values from your function calls into their placeholders.
          Then move through the query step-by-step and tell me where it fails. Thx.

          Reply

  7. Thank you for sharing this tool. I too am having the same issue with “Column1” not being recognized. Here is the error:

    An error occurred in the ‘’ query. Expression.Error: The column ‘Column1’ of the table wasn’t found.
    Details:
    Column1

    I tried your last suggestion, and then it got hung up on the Qty name with this error:

    An error occurred in the ‘’ query. Expression.Error: The name ‘QtyPer’ wasn’t recognized. Make sure it’s spelled correctly.

    It is spelled correctly so I’m not sure why it’s breaking down there. Any suggestions? Thank you. Appreciate your help.

    Reply

    • Be aware that PQ is case sensitive. Also, if you fill in the names of the columns, you have to enclose them in “”.
      Apart from that: If you are debugging like described in my last comment, you should be able to see in which step the query actually fails. Maybe this will help as well.

      Reply

      • Thanks for your reply. Could you tell me which columns are required to make the script work? I am assuming it’s just the parent, the component and the quantity per fields. Anything else like Unit of Measure required? Thanks.

        Reply

        • Hi rtuero,
          thanks for following this up: I’ve realized that this code will not work in non-English-versions, as it refers to “column1”, which has automatically been created by the code. Very sorry about that!!
          So I’ve replaced it now with Table.ColumnNames(Table){0} (Pseudocode) and it will hopefully work.
          Pls let me know if it now works for you.
          Thx – Imke

          Reply

  8. Thank you very much. This is very useful. Is there way to compare the start date and end date in the code by taking the smallest value?

    Here is an example:
    Parent Child Quantity From To
    Big Bundle 1 Bundle 1 1 1/1/1900 7/25/2016
    Big Bundle 1 Bundle 2 1 7/26/2016 10/3/2016
    Big Bundle 1 Bundle 1 1 10/4/2016 12/31/9999
    Bundle 1 Product 1 1 1/1/2015 12/31/9999
    Bundle 1 Product 2 1 1/1/2015 12/31/9999
    Big Bundle 1 1 1/1/2015 12/31/9999

    Here is the result, and you can see the date is not right.
    Child Quantity From To TotalQty SpacedPath PathExplode TopParentProduct
    Product 1 1 1/1/2015 12/31/9999 1 | | Product 1 Big Bundle 1/Bundle 1/Product 1 Big Bundle 1
    Product 1 1 1/1/2015 12/31/9999 1 | | Product 1 Big Bundle 1/Bundle 1/Product 1 Big Bundle 1

    Reply

    • Yes, but you should keep the tasks separate:
      1) First filter your table so that only the smallest for each PC-combination remains and then do the PC-flattening.
      2) Or, if your actual task is a bit more complicated: Create new keys for the parent and/or child before flattening: Like: Bundle1(1995-9999)

      Reply

  9. I am new to using M Code and I am getting a syntex error

    Expression.SyntaxError: Token RightParen expected

    I am in the USA – is it something about the comma being used (the code error is showing up in the first line at the first comma).

    Otherwise could it be the version of Excel I am using (I have tried 2010 and 2016 – the only two I have)

    Any suggestions? Thank you for a fantastic site!

    Reply

    • You’re probably missing a parenthesis as the error-message says. I’m also using the American version, so this shouldn’t cause the problem.
      You just have to compare the code for every parenthesis (also their shapes!!) and commas.

      Reply

Leave a Reply