Containerization of Orders Function

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.