Forum Discussion
Help me manipulate this data
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 ReorderedTroubleshooting 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
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