Forum Discussion

Data4Beer's avatar
Data4Beer
Frequent Visitor
8 years ago
Solved

Help me manipulate this data

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 ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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

     

  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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.

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

Also when invoking the recursive function, you must supply #"BI - BOM Material_table" instead of Source:

 

ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,#"BI - BOM Material_table"),
Data4Beer's avatar
Data4Beer
Frequent Visitor
8 years ago

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
  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    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.

  • Data4Beer's avatar
    Data4Beer
    Frequent Visitor
    8 years ago

    Hi Marcel

     

    Thanks very much for the help!

     

    Greatly appreciated.