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.

Data4Beer's avatar
Data4Beer
Frequent Visitor
8 years ago

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

 

 

 

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

Your step name with double quotes, should also have a #, as in:

#"step name"

  • Data4Beer's avatar
    Data4Beer
    Frequent Visitor
    8 years ago

    Now I am getting 'Token Comma Expected'

     

    • 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