Created
January 12, 2018 16:56
-
-
Save ImkeF/02bb408e1ed82577695ce91aaf442b6b to your computer and use it in GitHub Desktop.
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 |
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
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.