Slotting Calculation Challenge

[dcbuzzell]

Struggling to find the way to do this. Have a table of rows (product) called Table1, including a date column for ReadyToStart which indicates when it will be ready, and a date column WeekMonday which is the Monday of the week for ReadyToStart

Item	ReadyToStart	WeekMonday
1amb1	5/28/24	5/27/24
1amn1	5/29/24	5/27/24
1amt1	6/5/24	6/3/24

Another table called tblSlotting with a date column WeekMonday which gives the Monday of each week and a number column Slots for the number of items that can be produced that week.

WeekMonday	Slots
5/27/24	1
6/3/24	0
6/10/24	2

The number of product ReadyToStart in a given work week will usually exceed the Slots for its ReadyToStart week. Need to be able determine a StartDate in the ReadyToStart week until it equals the Slots; then for the next row move it to the following week with sufficient Slots, and continue this through all rows for Table1

The result should be:

Item	StartDate
1amb1	5/28/24
1amn1	6/3/24
1amt1	6/10/24

Any suggestions?

 

[dcbuzzell]

my mistake, the StartDate for 1amn1 should be 6/10/24.

 

[AlienSx]

let
    // list of slots
    tblSlotting = Excel.CurrentWorkbook(){[Name="tblSlotting"]}[Content],
    s = Table.TransformColumns(
        Table.SelectRows(tblSlotting, (x) => x[Slots] > 0), 
        {"Slots", (x) => List.Repeat({null}, x)}
    ),
    slots = List.Buffer(Table.ExpandListColumn(s, "Slots")[WeekMonday]),
    // products
    Table1 = Table.Sort(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "ReadyToStart"),
    prod = List.Buffer(Table1[WeekMonday]),
    // use slots
    find_slot = (k, skp) => 
        [
            i = k,
            pos = List.PositionOf(List.Skip(slots, skp), prod{i}, Occurrence.First, (c, v) => c >= v),
            slot_position = pos + skp,
            skip = slot_position + 1
        ],
    gen = List.Generate(
        () => find_slot(0, 0),
        (x) => x[i] < List.Count(prod) and x[pos] <> -1,
        (x) => find_slot(x[i] + 1, x[skip]),
        (x) => slots{x[slot_position]} 
    ),
    result = Table.FromList(
        List.Zip(Table.ToColumns(Table1) & {gen}),
        (x) => {x{0}, if x{3} is null then "no slot found" else if x{2} = x{3} then x{1} else x{3}},
        {"Item", "StartDate"}
    )
in
    result

 

[dcbuzzell]

Thank you very much. That makes perfect sense and I appreciate your response.