Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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,

 

No Replies