Bill of Materials (BOM) solution in Excel and PowerBI

Edit 12-Jan-18: Code and file updated (robustness & speed)

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:


In the file below, 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 (71) 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?


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


  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.


  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.

    any help would be appreciated


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


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


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


          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.


  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.

    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.


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


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


        • 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


  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


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


  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!


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


  10. Hi!
    When I try your solution (thx btw) with a subset of my own data I get the following error:
    “There weren’t enough elements in the enumeration to complete the operation”

    If you could point me in the right direction as to what has gone wrong I would greatly appreciate it.



    • Hi Frank,
      this will happen when your top-parent items don’t have a separate row with a blank parent item like Bill in this post:
      Actually, in that solution I’ve included a mechanism to cater for cases like that. So I’ve adjusted this BOM-code accordingly. Please check if it works for you and report back – I will then update the code in the article itself. Thanks!

      (Table as table, Parent as text, Child as text, Qty as text) =>
      Table = tblBOM,
      Parent = “ProductAssemblyID”,
      Child = “ComponentID”,
      Qty = “PerAssemblyQty”,
      ChgTypeKeyCols = Table.Buffer(Table.TransformColumnTypes(Table,{{Parent, type text}, {Child, type text}})),

      ReplaceNulls = Table.ReplaceValue(ChgTypeKeyCols ,null,””,Replacer.ReplaceValue,{Parent}),
      MissingParents = List.Buffer(List.Select(List.Difference(List.Distinct(Table.Column(ReplaceNulls , Parent)), List.Distinct(Table.Column(ReplaceNulls , Child))), each _ <> “”)),
      CleanTable = Table.Buffer(Table.Combine({ReplaceNulls , #table({Child, Parent}, List.Transform(MissingParents, each {_, “”}))})),

      // Start the iteration with the top-parents only
      SelectTopParents = Table.SelectRows(CleanTable , each Record.Field(_, Parent)=””),
      Custom1 = SelectTopParents,
      // Add Path-column where the necessary 2 fields: Child and Qty are collected
      AddPath = Table.AddColumn(Custom1, “Path”, each #table({“Path_”, “Qty_”}, {{Record.Field(_, Child), Record.Field(_, Qty)}})),
      // Combine Parent and Child of the BOM-table as long as there are still new children in the next iteration step & write the elements into the Path-table
      ResolveBOM = List.Generate(()=>
      [Result=AddPath, Level=0],
      each Table.RowCount([Result]) > 0,
      each [ Result = let
      A = Table.NestedJoin(ChgTypeKeyCols,{Parent},[Result],{Child},”NewColumn”,JoinKind.Inner),
      B = Table.ExpandTableColumn(A, “NewColumn”, {“Path”}, {“PathOld”}),
      C = Table.AddColumn(B,”Path”, each Table.Combine({[PathOld], #table({“Path_”, “Qty_”}, {{Record.Field(_, Child), Record.Field(_, Qty)}})}))
      in C,
      Level = [Level]+1 ]),
      ConvertToTable = Table.FromList(ResolveBOM, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      ExpandBOM1 = Table.ExpandRecordColumn(ConvertToTable, Table.ColumnNames(ConvertToTable){0}, {“Level”, “Result”}, {“Level”, “Result”}),
      ExpandBOM2 = Table.ExpandTableColumn(ExpandBOM1, “Result”, Table.ColumnNames(ExpandBOM1[Result]{1})),
      // Add a couple of fields/columns needed for the reports
      AddFields = Table.AddColumn(ExpandBOM2, “NewFields”, each [
      TotalQty = List.Product([Path][Qty_]),
      SpacedPath = Text.Repeat(” | “, [Level])&Record.Field(_, Child),
      PathItems = [Path][Path_],
      PathExplode = Text.Combine(PathItems, “/”),
      // PathWhereUsed = Text.Combine(List.Reverse(PathItems), “/”),
      TopParentProduct = PathItems{0} ]),
      ExpandNewFields = Table.ExpandRecordColumn(AddFields, “NewFields”, Record.FieldNames(AddFields[NewFields]{0})),
      // Create column indicating if item is purchase item
      PurchaseItems = Table.Buffer(Table.FromColumns({List.Difference(List.Distinct(Table.Column(ChgTypeKeyCols, Child)), List.Distinct(Table.Column(ChgTypeKeyCols, Parent)))})),
      MergePurchaseItem = Table.NestedJoin(ExpandNewFields,{Child},PurchaseItems,{Table.ColumnNames(PurchaseItems){0}},”NewColumn”,JoinKind.LeftOuter),
      ExpandPurchaseItem = Table.ExpandTableColumn(MergePurchaseItem, “NewColumn”, {Table.ColumnNames(PurchaseItems){0}}, {“PurchaseItem”}),
      Cleanup1 = Table.RemoveColumns(ExpandPurchaseItem,{“PathOld”, “Path”, “PathItems”}),
      Cleanup2 = Table.TransformColumnTypes(Cleanup1,{{Qty, type number}, {“TotalQty”, type number}})


    • I am getting the same problem as above when I used the Invoked Function – “query.Expression.Error:“There weren’t enough elements in the enumeration to complete the operation”” I using the same data a the example, any help would be greatly appreciated. Helen


      • I copied the code above and changed the “” (quotes) as I am in Ireland and it worked – thanks so much Helen


  11. Hi,
    Sorry for the late reply!
    I have tried the code now and it worked wonders.

    Thanks a lot!

    Kind Regards


    • Great! That’s what I wanted to hear 😉
      Thanks for the feedback Frank.

      (Have updated the code and file in the blogpost above)


  12. Hello, thank you very much for sharing the code and for the amazing post. However, I tried to used your code and got this error message (in Power BI):

    “Expression.Error: The column ‘Parent’ of the table wasn’t found.
    Can you please explain why I already defined the elements in the beginning but Power BI cannot refer to the input variables? Thank you.


    • Hi Stephanie,
      not sure that I understand your question, but it looks like something with the escape signs around a “Parent”-expression in your code is wrong.


      • Thank you for your reply. I fixed the expression and it works. But somehow I could not apply the query to the visualization. I used your code for my data file and its size is around 7MB. Also, in your video, your BOM has 3 level but will this code work for the BOM that has 8 levels?


        • Hi Stephanie,
          yes, it should work with any number of levels. Of course your need some RAM and CPU for this sort of calculation.
          What’s your current problem? That the calculation doesn’t finish at all?


          • Hi,
            Yes, it stopped calculating after the level 1 of the BOM. It didnt give out any error message or anything so I am not sure where it goes wrong.

          • Unfortunately I have no idea what might cause this, without looking at your file.

  13. Hi Imke,

    I love this solution, and it has worked well for me on exploding the BOM. Do you have a suggestion for creating the specific tree pathway in each BOM component in this example? In my specific BOM, there are multiple component trees within some parent items, and I would like to create a grouping with another column in PowerBI which follows the specific BOM parent item trees. Unfortunately, I’m still very new to M code, so I haven’t been able to draft a solution using your other example of flattening out the BOM.

    Any guidance you can provide? Thank you.


  14. Hi Imke , thanks a lot this is a great tool it me some time many thing went wrong as I am totally new M and languages of this sort


  15. Hi Imke,
    After copying the query and pressing invoke it keep telling me that error:
    Expression.Error: We cannot convert the value “tblBOM” to type Table.
    What should I do?
    Thanks in advance.


    • Hi Mohamed,
      Make sure to use the original code without any modification. I’ve just tested it and it worked fine for me.
      Are you feeding something with the name “tblBOM” into the function? If not, there is a code modification, as the function tries to reference elements from the debugging code, that has been out-commented.
      You have to feed a table into the first argument of the function.
      Cheers, Imke


  16. Pingback: Parent-Child Hierarchies with multiple parents in Power BI with Power Query

  17. Awesome model, it helped me to bring to one level a BOM list.
    Please let me know if I should cite someone in specific (rather than only the page).


    • Hi Carlos,
      great to hear that this was helpful for you.
      Please feel free to cite my name: Imke Feldmann.
      Thanks, Imke


  18. Thank you for sharing this. It works perfectly well. And it encourages me to learn M, which I had seemingly underestimated so far.


  19. My data has circular references between Parent and Child that result in the Invoked Function infinitely adding more rows to the database.

    The data looks something like this:

    Parent Child
    A B
    B C
    C A

    Is there a way to cap the number of iterations to a certain number or recognize and stop circular logic in another way?


    • Yes, you could adjust the code in row 23 like so:

          each Table.RowCount([Result]) > 0 and [Level] < 100,

      Which would limit the number of iterations to 100.


      • Thank you very much, Imke!
        I am now able to get an output, but am getting results where, per my earlier example, I get “ABCABCABCABCABC” (as expected per the code you suggested).

        I am wondering if code on row 23 can be further modified to stop when the function recognizes a repeat? As in , if the value in current Level matches the value in Level 0, stop the iteration, so that my example would return ABC

        Thank you very much for your help


  20. Thank you for sharing this funtion!

    I use the function result to select a specific father item from a parameter and add some other data from other queries.

    CriteriaBG = Select_BG,
    … = Table.SelectRows(Quelle, each ([TopParentProduct] = Select_BG))

    This works fine if the parameter is a TopParentProduct, but if not, the result is empty. But how can I select the tree of a child product? I have for example the following tree:

    Father Item
    Father Item\Child Item 1
    Father Item\Child Item 1\Child Item 2

    I want now to see the full tree for Child Item 1 and Child Item 2, but I don’t know how to filter the result to reach it.

    Do you have an idea? Thanks in Advance!


    • Sorry Sebastian,
      I don’t understand what you mean with “the full tree”.


  21. Hello Imke,

    I mean with full tree for child items, all items of the BOM starting from a Level > 0. I use your function also for calculation the product costs of a selected product. So I need a filter, which shows the bom of a user selected father or child item No. (child items can have also their own bom). When I filter the TopParentProduct depending on the selected parameter, I have no result, when the paramter is not a TopParentProduct .

    So change the filtered field to…

    = Table.SelectRows(Quelle, each Text.Contains([PathExplode], Select_BG)),

    But now I have a problem, that I get items in the bom created by your function, which are not part of the selected parameter/item. For example:

    Level 0 – Father Item 1 (only for understanding, not in the result when parameter = Child Item 1)

    Level 0 – Father Item 2 (only for understanding, not in the result when parameter = Child Item 1)

    Level 1 – Father Item 1 \ Child item 1 (has also a own bom)

    Level 1 – Father Item 2 \ Child item 1 (child item has more than one father item)

    Level 2 -> here follows now my needed bom of child item 1 with other levels and items

    So I was thinking about to Filter something like [Level] > Min([Level]). Because I never need level = 0 and if th user want to start the bom from a child item, I also do not need their father items. In my example [Level] > 1.

    I hope it is more comprehensible.


    • Hi Sebastian,
      currently I don’t see a way to achieve this without major recoding, unfortunately.
      I can provide this as a consultancy service.


  22. Hello Imke,

    thank you for taking your time to answer my question. I sensed that it is not that easy. Also thanks for the offer, but in this case I have to find a way by my own 😉


  23. Hello,

    I am not able to put any filter in the invoked function result,it is hanging for long time.


    • Hard to tell from the distance. But you could try to use a Table.Buffer on the BOM result before filtering.


  24. I have your function working perfectly in Excel with PowerQuery version 2.46.4732.721 32-bit. Thank you so much for this!


  25. I’m using your function in PBI to plan my production. I was trying this a long ago without success and today it worked so easily. Thank you so much!

    In my case each process has a diferent output quantities that isn’t equal to the input of the next process it will be used. Because it is used in multiple next steps. So I used in the “Qty” parameter a calculated column that is the child quantity divided by the parent quantity and it worked perfectly.


  26. Now I need to calculate QTY considering that some some items follow a FOQ (fixed order quantity) lot sizing policy. Any help?


    • I haven’t done this so far.
      As that sound a bit niche, I would only offer this as a consultancy service.


  27. Hi I tried this formula but it misses out many parent parts. Would you have an idea as to why this is happening?


  28. hELLO, I tried this code on my data. So basically I’e two colums, one of the Head Part and the other child part. No wsome of these child parts separately appear in the Head part column too. But when i ran this code, it has selectively taken only a few head parts, but their child parts and subsequent child parts if any have been correctly mapped. But my problem is all 602 parts in the head part column are not there. Could you please help as to why that is?


  29. Hi,

    thank you for this nice piece of code!
    It works really fine on my dataset and already gave me a good inside.

    One thing is a little show-stopper:

    While all children attributes that are stored in futher columns are preserved, the parents loose all those data in the new table.

    As an Example, I have a “parts on Stock” Column. In the parent rows, this value is “null” after using this script.

    I’m not deep enough in M-Coding to find the reason myself so maybe anyone here could help? WOuld be much appreciated.


    • Hi Bastian,
      that’s correct. I did that for performance reasons.
      But you can add a step at the end where you merge the results from the BoM transformation with your original data and expand the other columns that you want to see.


Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: