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:
-
Explosion
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| (Table as table, Parent as text, Child as text, Qty as text) => | |
| let | |
| /* Debug parameters | |
| 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}}) | |
| in | |
| Cleanup2 |
In the file below, 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: 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.
If you’re looking for a way to do this within native Excel instead of Power Query, you can find some pre-built solutions here: https://www.simplesheets.co/bill-of-materials
Enjoy & stay queryious 😉
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.
Will need to find out if that’s allowed.
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.
Thanks a lot for your deeper explanation about the features and pros and cons of each languages.
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 () )
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:
All I did was delete invoked table and try to invoke it on same sample data that you have in workbook – it throws following error: http://imgur.com/a/5Ng0L
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.
Works like a charm now – thanks.
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
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) =>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.
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.
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
Thank you. That worked!
Thx for feedback!
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)
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.
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.
Thanks
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: http://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-powerbi/
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) =>
let
/*
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}})
in
Cleanup2
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
Thanks for the heads-up Helen!
Unfortunately, WordPress kills the straight quotes 🙁
Hi,
Sorry for the late reply!
I have tried the code now and it worked wonders.
Thanks a lot!
Kind Regards
Frank
Great! That’s what I wanted to hear 😉
Thanks for the feedback Frank.
(Have updated the code and file in the blogpost above)
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.
Details:
Parent”
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?
/Imke
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.
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.
Sorry, don’t have a solution for that in my sleeve.
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
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.
Details:
Value=tblBOM
Type=Type
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
Pingback: Parent-Child Hierarchies with multiple parents in Power BI with Power Query
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).
Regards,
Carlos
Hi Carlos,
great to hear that this was helpful for you.
Please feel free to cite my name: Imke Feldmann.
Thanks, Imke
Thank you for sharing this. It works perfectly well. And it encourages me to learn M, which I had seemingly underestimated so far.
Great to hear 🙂
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:
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
I’m afraid, but I don’t think that/how this would be possible.
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”.
/Imke
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.
/Imke
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 😉
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.
I have your function working perfectly in Excel with PowerQuery version 2.46.4732.721 32-bit. Thank you so much for this!
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.
That’s great to hear – thanks for the feedback !
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.
/Imke
Hi I tried this formula but it misses out many parent parts. Would you have an idea as to why this is happening?
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?
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.
/Imke
Thank you for sharing this BOM Function. I used it in Power BI against the MBOM table from my MRP application database. It seemed to work in Power Query. However, once I attempt to Load the new table, it won’t finish loading. There are no errors, just shows the loading symbol (circle) next to the table name and never finishes. Any thoughts on how to troubleshoot this issue?
Thanks,
Joe
Hi Imke! I’ve used this fabulous solution a few times but I’m coming across a new challenge. I want to make sure it truly is as complicated as it seems before I deviate from your solution.
Imagine I have a standard BOM. Using your function, it is easy to take a table of top-level item orders and drill down to the total qty of component parts needed. But what if I also sell each component part? Let’s say I sell component part 7 which is the 3rd level of one BOM, e.g. 1|4|7, and the 4th level of another BOM, e.g. 2|5|6|7
My initial thought is that we need a top-level record for each item that we’re selling. If we’re selling ALL of the products, does that mean I have to iterate through all records so that I have a top-level path to start each BOM? If that’s the case, I’m thinking we’re heading out of Power Query territory (unless you have some magic up your sleeve). Thanks for your time, Imke!
Hi Parker,
from what I understand, I would approach it like so:
1) Do the (recursive) BoM flattening once (maybe in a separate dataflow)
2) “Cut” out all relevant sales products from the flattened sequences (that’s a simple operation using List.Range or Table.Range: List.PositionOf will give you the start or the range). Then use List.Product to generate the total amounts. (No complicated recursion needed here, as the series are known)
Performance-wise: Where possible, avoid operating over nested objects, instead expand them first. Don’t see why this wouldn’t work in PQ, but maybe my understanding of the requirement is not correct.
BR, Imke
Hi Imke,
thank you for this code!
I do have a different set of circumstances that maybe you could help with.
EX part A we sell to customer A as a set of 4, Also part A we sell to customer B but only 1 of 4 (0.25 qty). In the current code it only shows customer B as the Top Parent Product, which is expected, however I want to see all possible customer parts as the Top Parent Product because the child part qty rolls up differently for each customer part. The spaced path and level should reset with each customer part.
Imke, this is a fantastic tool and thank you for sharing it.
As a quesiton on this to understand whether there would be an easy solution or if it would be a significant re-write, the data set I am using has parent and children that repeat in the data based on an additional factor (manufacturing plant). Each of the parents and children can possibly be made in multiple plants. Using this explosion code, it incorrectly will explode out a scenario for an child product where the manufacturing plant for the child does not match the plant for the parent, and duplicate the record.
I’d like to be able to add “Plant” as an additional match factor during the table join and explosion process, but my attempts so far have not been successful. Is that a relatively simple process and I’m just unfamiliar with the syntax, or a major re-write?
Imke could be possible that you have duplicates in initial table due to possibility of have different version of bom by date? I cant find filter for only lines valids
Hello
Is it possible to add the new columns on the same table chosen in the function?
How would the Code look like?
hi can you please advise how can i do the similar in Sql sever
This function is wonderful! Thank you for sharing! Any thoughts on how to make this run on a table of multiple boms? I have an extra column called Top Level.
How can I filter only the BoM for a assies in a orderTable, with columns [order, assy]? These can be assies on different levels. A merge query afterwards is too heavy (and thus too slow)
Hello, Thank you for the code. But I’m getting nulls in the data for all column coming from my source.