Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Containerization of Orders Function

Hi All,   I am requesting help to build a function that assigns SKU's within Orders to a container. Each Order uses containers of a certain size which is determined in the Max Bin Qty. SKU Qty is t...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi Anonymous ,

    if my understanding is correct, you can use this function on the order-level:  

     

    // fnContainers
(Source, MaxBinQtyColName as text, SKUQtyColName as text ) => 
let
//Source = #"myTable (2)"{1}[Partition],
//MaxBinQtyColName = "Max Bin Qty (Function Input)",
//SKUQtyColName = "SKU Qty (Function Input)",
    MaxBinQty = List.First(Table.Column(Source, MaxBinQtyColName)),
    BufferedList = List.Buffer(Table.Column(Source, SKUQtyColName)),
    Output = List.Skip(List.Generate( () =>
                [RT = 0, Bin = 0, Counter = 0],
                each [Counter] <= List.Count(BufferedList),
                each [
                    RT = if  ( [RT] + BufferedList{[Counter]} ) > MaxBinQty then BufferedList{[Counter]} else [RT] + BufferedList{[Counter]} ,
                    Bin = if ( [RT] + BufferedList{[Counter]} ) > MaxBinQty then [Bin] + 1 else [Bin],
                    Counter = [Counter] + 1 
                ],
                each [Bin] + 1

    ))
in
    Output

     

    To apply it, your code would look like so (also, see the attached file): 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSAVFgEkgYg1lKsTpwSSMoiVXSGKLTFCZphCxpApE0h0kagyWNkO00hdlriCwHsdIEq5w5hITJGSHLWUDchCQXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order (Function Input)" = _t, #"SKU ID (Function Input)" = _t, #"SKU Qty (Function Input)" = _t, #"Max Bin Qty (Function Input)" = _t, #"Container # (Function Output)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order (Function Input)", Int64.Type}, {"SKU ID (Function Input)", Int64.Type}, {"SKU Qty (Function Input)", Int64.Type}, {"Max Bin Qty (Function Input)", Int64.Type}, {"Container # (Function Output)", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order (Function Input)"}, {{"Partition", each _}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "Container", each fnContainers([Partition], "Max Bin Qty (Function Input)", "SKU Qty (Function Input)")),
    #"Added Custom" = Table.AddColumn(  #"Invoked Custom Function", 
                                        "Result", 
                                        each Table.FromColumns(Table.ToColumns([Partition]) & {[Container]}, Table.ColumnNames([Partition]) & {"Container"})),
    Custom1 = Table.Combine(#"Added Custom"[Result])
in
    Custom1

    This technique should also work fairly fast on large tables.

     

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi Anonymous ,
Thank you for providing your sample data. Based on that data would you also posted your expected outcome?
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Nathaniel_C  In the Sample Data the Headers display the columns that are input columns and what column that shows the expected outcome of the function. The function should only create 1 new column assigning a container number to each SKU within an Order. 

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Anonymous ,
    So another way to look at this would be, a running total of how many containers are needed to fill an order? We are not really assigning a container # to a SKU, because in some cases, a SKU might go into more than 1 container, correct?

    Will be offline for awhile...
    Nathaniel

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Nathaniel_C  That is correct, Keeping the results at the SKU level (meaning Container # will repat itself if an Order has multiple SKU's). You will not need to break out a SKU across multiple containers. Said another way, you will not need to break a record out into 2 records to fill up 1 container and start another. If the entire SKU Qty cannot fit into the remaining capacity of a container then a new container is assigned to that SKU. 

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        Hi Anonymous 
        This solution is based on your data submitted. I used your SKU ID to know my place in the table.  You may need to add an Index column in Power Query to perform that function instead.
        Let me know if you have any questions.

        If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
        Nathaniel

        Sum of SKU QTY = SUM(myTable[SKU Qty (Function Input)])

         

        RT of SKU QTY =
VAR _SKU_ID =
    MAX ( myTable[SKU ID (Function Input)] )
VAR _order =
    MAX ( myTable[Order (Function Input)] )
VAR _SKU_QTY =
    MAX ( myTable[SKU Qty (Function Input)] )
VAR _maxBinQty =
    MAX ( myTable[Max Bin Qty (Function Input)] )
VAR _calc =
    CALCULATE (
        [Sum of SKU QTY],
        ALLEXCEPT ( myTable, myTable[Order (Function Input)] ),
        myTable[SKU ID (Function Input)] <= _SKU_ID
    )
RETURN
    ROUNDUP ( DIVIDE ( _calc, _maxBinQty ), 0 )