Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone!
I am struggling to solve this problem. I have bill of materials which contains anInventoryID's and the materials.
In this example, there are 4 Work in Progress phases (inventory items), W1,W2,W3,FG. From W2 onwards, the item makes use of the previous WIP item and eventually produces a finished good. See the first table below:
I need to be able to manipulate and add a column that shows what finished good inventoryID is being produced such as the table below:
I would appreciate it if someone could assist me.
Thanks!
Solved! Go to Solution.
In the query below, recursive function ExplodeBOM is used as part of a solution that is independent from the sort order of the original table.
As a prerequisite, all materials must have the same case for their codes, e.g. w1br001 is not the same as W1BR001.
This also allows for subassemblies to appear in multiple finished goods.
In each iteration, a new BOM level is added to the resulting table.
let Source = Table1, SelectedFinishedGoods = Table.NestedJoin(Source,{"InventoryID"},Table1,{"Material Added"},"Table1",JoinKind.LeftAnti), RemovedJoinColumn = Table.RemoveColumns(SelectedFinishedGoods,{"Table1"}), AddedFinishedInventoryID = Table.Buffer(Table.DuplicateColumn(RemovedJoinColumn, "InventoryID", "Finished InventoryID")), ExplodeBOM = (TableSoFar as table, PreviousTable as table) as table => let SelectedRemainingRecords = Table.NestedJoin(PreviousTable,{"InventoryID"},TableSoFar,{"InventoryID"},"JoinColumn",JoinKind.LeftAnti), RemainingRecords = Table.RemoveColumns(SelectedRemainingRecords,{"JoinColumn"}), SelectedNewRecords = Table.NestedJoin(RemainingRecords,{"InventoryID"},TableSoFar,{"Material Added"},"RemainingRecords",JoinKind.Inner), NewRecords = Table.ExpandTableColumn(SelectedNewRecords, "RemainingRecords", {"Finished InventoryID"}), NewTable = Table.Buffer(TableSoFar & NewRecords), Result = if Table.IsEmpty(SelectedRemainingRecords) then TableSoFar else @ExplodeBOM(NewTable, RemainingRecords) in Result, ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,Source), Sorted = Table.Sort(ExplodedBOM,{{"Finished InventoryID", Order.Ascending}, {"Material Added", Order.Ascending}}), Reordered = Table.ReorderColumns(Sorted,{"Finished InventoryID", "InventoryID", "Material Added"}) in Reordered
Troubleshooting your issues takes me a multitude of time that was required to come up with a solution in the first place.
Again, your ExplodedBOM step is wrong.
It should be:
ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,#"Removed Duplicates"),
My suggestion would be not to use any solution you don't understand.
Hi @Data4Beer
Try this
let Source = Excel.CurrentWorkbook(){[Name="TableName"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"InventoryID", type text}, {"Material Added", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.PositionOf([InventoryID],"FGbr") >= 0 then [InventoryID] else null), #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}), #"Filtered Rows" = Table.SelectRows(#"Filled Up", each not Text.Contains([InventoryID], "FGbr")), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Custom", "InventoryID", "Material Added"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Finished Inventory ID"}}) in #"Renamed Columns"
Basically we follow these steps
Step#1 Add a custom column using this formula
=if Text.PositionOf([InventoryID],"FGbr") >= 0 then [InventoryID] else null
Step #2 : Select the custom Column>>> Goto "Transform" tab>>FillUp
You will get
Step#3 Filter the InventoryID column
"Does not contain FGbr"
Step#4: Rename the Custom Column and Reorder it
Hi Zubair
Thank you for your response.
My only issue with this method is that the data does not reflect a sequential order as the example does. Therefore the 'Fillup' function is not a solution. Thank you for your time and effort.
In the query below, recursive function ExplodeBOM is used as part of a solution that is independent from the sort order of the original table.
As a prerequisite, all materials must have the same case for their codes, e.g. w1br001 is not the same as W1BR001.
This also allows for subassemblies to appear in multiple finished goods.
In each iteration, a new BOM level is added to the resulting table.
let Source = Table1, SelectedFinishedGoods = Table.NestedJoin(Source,{"InventoryID"},Table1,{"Material Added"},"Table1",JoinKind.LeftAnti), RemovedJoinColumn = Table.RemoveColumns(SelectedFinishedGoods,{"Table1"}), AddedFinishedInventoryID = Table.Buffer(Table.DuplicateColumn(RemovedJoinColumn, "InventoryID", "Finished InventoryID")), ExplodeBOM = (TableSoFar as table, PreviousTable as table) as table => let SelectedRemainingRecords = Table.NestedJoin(PreviousTable,{"InventoryID"},TableSoFar,{"InventoryID"},"JoinColumn",JoinKind.LeftAnti), RemainingRecords = Table.RemoveColumns(SelectedRemainingRecords,{"JoinColumn"}), SelectedNewRecords = Table.NestedJoin(RemainingRecords,{"InventoryID"},TableSoFar,{"Material Added"},"RemainingRecords",JoinKind.Inner), NewRecords = Table.ExpandTableColumn(SelectedNewRecords, "RemainingRecords", {"Finished InventoryID"}), NewTable = Table.Buffer(TableSoFar & NewRecords), Result = if Table.IsEmpty(SelectedRemainingRecords) then TableSoFar else @ExplodeBOM(NewTable, RemainingRecords) in Result, ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,Source), Sorted = Table.Sort(ExplodedBOM,{{"Finished InventoryID", Order.Ascending}, {"Material Added", Order.Ascending}}), Reordered = Table.ReorderColumns(Sorted,{"Finished InventoryID", "InventoryID", "Material Added"}) in Reordered
Hi Marcel
Thank you for your detailed reponse.
I have tried the following from the example.
I get a 'token Identifier expected' error
Where:
InventoryID = InventoryID_2
Material Added = InventoryID
Your step name with double quotes, should also have a #, as in:
#"step name"
Now I am getting 'Token Comma Expected'
You need to provide the step name from the previous step.
Actually you also need to replace "Source" with the step name of the previous step.
In my example, Source was the same as Table1, which is - in your case - the name of the #"BI....."step.
(I'm not going to type over from your pictures, so if you need further clarity, please provide code as text, instead of pictures).
let Source = OData.Feed("https://example.co.za/odata/example"), #"BI - BOM Material_table" = Source{[Name="BI - BOM Material",Signature="table"]}[Data], SelectedFinishedGoods = Table.NestedJoin(Source,{"InventoryID_2"},BI - BOM Material_table,{"InventoryID"},"BI - BOM Material_table",JoinKind.LeftAnti), RemovedJoinColumn = Table.RemoveColumns(SelectedFinishedGoods,{"BI - BOM Material_table"}), AddedFinishedInventoryID = Table.Buffer(Table.DuplicateColumn(RemovedJoinColumn, "InventoryID_2", "Finished InventoryID")), ExplodeBOM = (TableSoFar as table, PreviousTable as table) as table => let SelectedRemainingRecords = Table.NestedJoin(PreviousTable,{"InventoryID_2"},TableSoFar,{"InventoryID_2"},"JoinColumn",JoinKind.LeftAnti), RemainingRecords = Table.RemoveColumns(SelectedRemainingRecords,{"JoinColumn"}), SelectedNewRecords = Table.NestedJoin(RemainingRecords,{"InventoryID_2"},TableSoFar,{"InventoryID"},"RemainingRecords",JoinKind.Inner), NewRecords = Table.ExpandTableColumn(SelectedNewRecords, "RemainingRecords", {"Finished InventoryID"}), NewTable = Table.Buffer(TableSoFar & NewRecords), Result = if Table.IsEmpty(SelectedRemainingRecords) then TableSoFar else @ExplodeBOM(NewTable, RemainingRecords) in Result, ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,Source), Sorted = Table.Sort(ExplodedBOM,{{"Finished InventoryID", Order.Ascending}, {"InventoryID", Order.Ascending}}), Reordered = Table.ReorderColumns(Sorted,{"Finished InventoryID", "InventoryID_2", "InventoryID"}) in Reordered
Hi Marcel
I would appreciate the clarity. See code attached.
Thanks
Your code, with step SelectedFinishedGoods adjusted:
let Source = OData.Feed("https://example.co.za/odata/example"), #"BI - BOM Material_table" = Source{[Name="BI - BOM Material",Signature="table"]}[Data], SelectedFinishedGoods = Table.NestedJoin(#"BI - BOM Material_table",{"InventoryID_2"},#"BI - BOM Material_table",{"InventoryID"},"BI - BOM Material_table",JoinKind.LeftAnti), RemovedJoinColumn = Table.RemoveColumns(SelectedFinishedGoods,{"BI - BOM Material_table"}), AddedFinishedInventoryID = Table.Buffer(Table.DuplicateColumn(RemovedJoinColumn, "InventoryID_2", "Finished InventoryID")), ExplodeBOM = (TableSoFar as table, PreviousTable as table) as table => let SelectedRemainingRecords = Table.NestedJoin(PreviousTable,{"InventoryID_2"},TableSoFar,{"InventoryID_2"},"JoinColumn",JoinKind.LeftAnti), RemainingRecords = Table.RemoveColumns(SelectedRemainingRecords,{"JoinColumn"}), SelectedNewRecords = Table.NestedJoin(RemainingRecords,{"InventoryID_2"},TableSoFar,{"InventoryID"},"RemainingRecords",JoinKind.Inner), NewRecords = Table.ExpandTableColumn(SelectedNewRecords, "RemainingRecords", {"Finished InventoryID"}), NewTable = Table.Buffer(TableSoFar & NewRecords), Result = if Table.IsEmpty(SelectedRemainingRecords) then TableSoFar else @ExplodeBOM(NewTable, RemainingRecords) in Result, ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,Source), Sorted = Table.Sort(ExplodedBOM,{{"Finished InventoryID", Order.Ascending}, {"InventoryID", Order.Ascending}}), Reordered = Table.ReorderColumns(Sorted,{"Finished InventoryID", "InventoryID_2", "InventoryID"}) in Reordered
Also when invoking the recursive function, you must supply #"BI - BOM Material_table" instead of Source:
ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,#"BI - BOM Material_table"),
Hi Marcel
When I try run the query, powerbi crashes. Please will you review the code attached.
Here is a link to the data:
https://www.dropbox.com/sh/ypsalwssnhe8nip/AACbNw3JwjWzby1oSBIR83hYa?dl=0
let Source = OData.Feed("https://example.co.za/odata/example"), #"BI - BOM Material_table" = Source{[Name="BI - BOM Material",Signature="table"]}[Data], #"Filtered Rows" = Table.SelectRows(#"BI - BOM Material_table", each Text.StartsWith([Materials], "W") or Text.StartsWith([Materials], "FG")), #"Removed Duplicates" = Table.Distinct(#"Filtered Rows"), SelectedFinishedGoods = Table.NestedJoin(#"Removed Duplicates",{"InventoryID"},#"Removed Duplicates",{"Materials"},"BI - BOM Material_table",JoinKind.LeftAnti), RemovedJoinColumn = Table.RemoveColumns(SelectedFinishedGoods,{"BI - BOM Material_table"}), AddedFinishedInventoryID = Table.Buffer(Table.DuplicateColumn(RemovedJoinColumn, "InventoryID", "Finished InventoryID")), ExplodeBOM = (TableSoFar as table, PreviousTable as table) as table => let SelectedRemainingRecords = Table.NestedJoin(PreviousTable,{"InventoryID"},TableSoFar,{"InventoryID"},"JoinColumn",JoinKind.LeftAnti), RemainingRecords = Table.RemoveColumns(SelectedRemainingRecords,{"JoinColumn"}), SelectedNewRecords = Table.NestedJoin(RemainingRecords,{"InventoryID"},TableSoFar,{"Materials"},"RemainingRecords",JoinKind.Inner), NewRecords = Table.ExpandTableColumn(SelectedNewRecords, "RemainingRecords", {"Finished InventoryID"}), NewTable = Table.Buffer(TableSoFar & NewRecords), Result = if Table.IsEmpty(SelectedRemainingRecords) then TableSoFar else @ExplodeBOM(NewTable, RemainingRecords) in Result, ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,#"BI - BOM Material_table"), Sorted = Table.Sort(ExplodedBOM,{{"Finished InventoryID", Order.Ascending}, {"Materials", Order.Ascending}}), Reordered = Table.ReorderColumns(Sorted,{"Finished InventoryID", "InventoryID", "Materials"}) in Reordered
Troubleshooting your issues takes me a multitude of time that was required to come up with a solution in the first place.
Again, your ExplodedBOM step is wrong.
It should be:
ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,#"Removed Duplicates"),
My suggestion would be not to use any solution you don't understand.
Hi Marcel
Thanks very much for the help!
Greatly appreciated.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |