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.
