Forum Discussion
Adaptation of an existing Power Query function for BOM explosion
Hi all,
I am using the Power Query function for BOM explosion from BiCCountant
https://www.thebiccountant.com/2017/05/08/dynamic-bill-of-material-bom-solution-in-excel-and-powerbi/
Below is a snippet of the code.
In my case, the Top Parent Product is already in a column and does not need to be read by the iteration.
How can I adapt the function so that it simply takes the column with the Top Parent Product and then performs the steps that follow?
(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,