How to reschedule EDD based on production capacity - Power Query

Capacity for Offset will be 600,000. That was dummy. We can also, increase the calendar size. No problem at all. There is total 31 columns in the orginal table. and currently have 10 product types and 75k rows. daily orders will intake and size will grow day by day.

There is a column named CS Comment with a tag Urgent. I said if there is urgent, leave it as it is. Meaning don't include in the capacity logic like quantity > capacity.

I have tried your code, with the smaller version OrderDetails table I provided, working fine. But not working with the bigger version. after attaching orginal table with 31 columns, steps producing list only showing 19 columns, so unable to find index, like in orginal table index of EDD is 30 but can't find that value. Why this is happening I don't know.

I think I need to try spliting the table. One with columns you have used and other with all the remaining column with full hight which is currently 75k and will grow day by day. Once found assign EDD then merge back to the reamaining part.

Let me try and get back to you.

For offset, it is not working or weird result. All assigned EDD for that product should not exceed more than October 2025, it should arround october 15 to 20.

Please extend the duration of calendar current date + 90 days, in the calendar table M code.

Here I am attaching all the rows of the orginal table and exact product type capacity. Also, for performance I only consider the non null EDD. changed your code a little. Yearly holiday table, already merged with the calendartable. so you do not need to use yearly holiday table.

let
    // fx to find position of working day in the list
    wd_position = (d as date) => List.PositionOf(working_days, d, Occurrence.First),
    // list of working days
    working_days = List.Buffer(
        List.Sort(
                Table.SelectRows(CalendarTable, (x) => x[Is Working Day] = "True")[Date]
        )
    ),
    // capacity by product type
    caps = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(Capacity))),

    // Spliting Tables
    selectedColumns = {"Date", "Order Code", "Customer", "Product Type", "CS Approved Date", "Order Qty", "Estimated Delivery Date", "EDD"},
    orderDetails_Selected = Table.SelectColumns(OrderDetails, selectedColumns),

    // Creating 2 Tables With. One with null and other with non null
    OrderDetailsWithNullEDD = Table.SelectRows(orderDetails_Selected, each [EDD] = null),

    // With non null EDD
    OrderDetailsWithNonNullEDD = Table.SelectRows(orderDetails_Selected, each [EDD] <> null),

    // Tables with the remaining columns
    remainingColumns = List.Difference(Table.ColumnNames(OrderDetails), selectedColumns),
    orderDetails_Remaining = Table.SelectColumns(OrderDetails, remainingColumns),


    // orders sorted by prod type and estimated DD
    sorted = Table.Sort(OrderDetailsWithNonNullEDD, {"Product Type", "EDD", "CS Approved Date"}),
    // and >> to list
    orders = List.Buffer(Table.ToList(sorted, (x) => x)),
    // go over the list
    processing = List.Generate(
        () => [
            i = 0,
            prod = orders{0}{3},
            iw = wd_position(orders{0}{7}),
            cap = Record.Field(caps, prod),
            flag = orders{0}{5} > cap,
            current_qty = if flag then 0 else orders{0}{5},
            cap_usage = current_qty
            //delivery_date = orders{0}{7}
        ],
        (x) => x[i] < List.Count(orders),
        (x) => [
            i = x[i] + 1,
            prod = orders{i}{3}, // current order's product type
            same_prod = x[prod] = prod, // check if prod type is the same as last order 
            cap = if same_prod then x[cap] else Record.Field(caps, prod), // current capacity
            flag = orders{i}{5} > cap, // check if qty is greater than cap
            current_qty = if flag then 0 else orders{i}{5}, // this order usage quantity
            cap_is_full = (not same_prod) or 
                (x[cap_usage] + current_qty) > cap or
                working_days{x[iw]}? is null or
                orders{i}{7} > working_days{x[iw]}, // reset capacity??
            cap_usage = if cap_is_full then current_qty else x[cap_usage] + current_qty, // capacity used
            iw = if (not same_prod) then wd_position(orders{i}{7}) // position in working days calendar
                else if cap_is_full  
                    then x[iw] + 1 
                    else x[iw]
        ],
        (x) => orders{x[i]} & (if working_days{x[iw]}? is null 
            then {null, null, null, "calendar is full"}
            else {x[cap], x[cap_usage], working_days{x[iw]}, if x[flag] then "order out of capacity" else null}
        )
    ),
    result = Table.FromList(processing, (x) => x, Table.ColumnNames(sorted) & {"capacity", "usage", "assigned_EDD", "flag"}),
    #"Changed Type" = Table.TransformColumnTypes(result,{{"Order Qty", Int64.Type}, {"assigned_EDD", type date}})
in
    #"Changed Type"

Here is the updated capacity table:

Product Type                    Capacity

for each day, offset should maintain showing orders total sum less than 600,000. other product will follow the same. Any whole order unable to fit that date, spill over to the next date, which must be working date. when spilling consider the spill orders + where you spilling, consider those order also, to check capacity logic.

Any whole order value > capacity, leave it as it is. Also, same for Urgent, Urgent (Revised), Urgent (Additional) in the cs comment column, just leave it as it is. 

Hope this info helps. Kindly check attached file here.