Skip to content

Instantly share code, notes, and snippets.

@ImkeF
Created January 12, 2018 16:56
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ImkeF/02bb408e1ed82577695ce91aaf442b6b to your computer and use it in GitHub Desktop.
Save ImkeF/02bb408e1ed82577695ce91aaf442b6b to your computer and use it in GitHub Desktop.
(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
@mohamedabdelaziz15
Copy link

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

@ImkeF
Copy link
Author

ImkeF commented Nov 18, 2019

Sorry @mohamedabdelaziz15 , just recognized your comment now: You have to remove the quotes from the table name in the first function argument. so Query1(tblBOM... instead of Query1("tblBOM"...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment