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 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!

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.

15 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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"

     

     

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Data4Beer

       

      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

       

       

       

       

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Data4Beer

         

        Step#3 Filter the InventoryID column

        "Does not contain FGbr"

         

         

         

        Step#4: Rename the Custom Column and Reorder it