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

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

Data4Beer's avatar
Data4Beer
Frequent Visitor
8 years ago
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

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    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

     

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