Mapping stock by delivery date and qty to order requirement and date order can be fulfilled

[Anonymous]

Hi Experts, after alot of trial and error I have managed to merge my 2 tables, one that shows "Part" "delivery qty" and Supplier Delivery Date" this table is then merged so that intial delivery dates match the order "Datefrom" column range.

The m code below covers that part of the problem, I would now like to do the following if its possible with Power query?

Column G "Stock" is what we have currently in the warehouse.

Column D "Qty required" is what qty i need to fullfill the order.

The first row shows "Qty required" = 10 and "Stock" = 14 the "RT Stock+order" would = 4

I then require a further 10 on the next row which I dont have in stock but covered by orders to fullfill.

I need to allocate the "Delivery qty" in FIFO to cover the deficite, this order can only be fullfilled once the delivery of 4 comes in on the 12/10/23 and an order of 10 on the 16/10/23.

I need to show the latest Date from any single or combination of deliverys to fullfill the order and also calculate the new "RT Stock + Order" subtracting the 10 away from the 4 in stock + the 4+10 orders required to fullfill the requirement.

This has to then continue down each row if there are Deliverys avaiable to cover demand then they need to be allocated into column H and the Date moved to Column I.

I hope this makes sense and someone can help me with this problem as I have spent many hours trying different solutions with no success. Is it possible in power query or is this more suited to AI.

Many thanks

let

    Source = Table.NestedJoin(sales, {"Part"}, SupplierDates, {"Part"}, "SupplierDates", JoinKind.LeftOuter),

    #"Match delivery Dates" = Table.AddColumn(Source, "Qty Ordered", each let datefrom=[Datefrom] in

let dateuntil=[Dateuntil] in

Table.SelectRows(SupplierDates, each [Date]<=datefrom and [Date]>=dateuntil)),

    #"Expanded Qty Ordered" = Table.ExpandTableColumn(#"Match delivery Dates", "Qty Ordered", {"Qty", "Date"}, {"Qty", "Date"}),

    #"Removed Columns" = Table.RemoveColumns(#"Expanded Qty Ordered",{"SupplierDates"}),

    #"Remove duplictaes" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),

    #"Added Custom1" = Table.AddColumn(#"Remove duplictaes", "Custom", each [Index]-1),

    #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Custom"}, #"Added Custom1", {"Index"}, "Added Custom1", JoinKind.LeftOuter),

    #"Sorted Rows1" = Table.Sort(#"Merged Queries",{{"Datefrom", Order.Ascending}}),

    #"Expanded Added Custom2" = Table.ExpandTableColumn(#"Sorted Rows1", "Added Custom1", {"Qty", "Date"}, {"Added Custom1.Qty", "Added Custom1.Date"}),

    #"Added Conditional Column" = Table.AddColumn(#"Expanded Added Custom2", "Custom.1", each if [Qty] = [Added Custom1.Qty] then null else [Qty]),

    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.2", each if [Date] = [Added Custom1.Date] then null else [Date]),

    #"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column1",{"Qty", "Index", "Custom", "Added Custom1.Qty","Added Custom1.Date","Date"}),

    #"Remove duplicates end" = Table.RenameColumns(#"Removed Columns2",{{"Custom.1", "Delivery qty"},{"Custom.2","Supplier Delivery Date"}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Remove duplicates end",{{"Supplier Delivery Date", type date}})

in

    #"Changed Type"

[lbendlin]

Use a proper resource management tool. Power BI isn't one.