Allocation model in Power query

Question

I want to create a simple allocation model in power query which will allocate inbound inventory to open orders. I have 2 tables as shown below. The first table is showing the open orders by SKU and then second table is showing inbound inventory by SKU which are going to available to allocate on multiple dates. The final result would be a table that shows allocated units for each SKU and the dates on which they got fully allocated. If it is not fully allocated then the last date of allocation to be available.

Order and Inbound Tables

Final Table after Allocation

I have tried adding index to inbound inventory by date available and then using helper columns to allocate but it is not that robust and needs tweaking as the inbound table would change almost every day.

I am thinking of using iterative logic to allocate units as I have done it before in VBA with loop function but I am not able to figure this out.

Any help will be appreciated. Thanks.

Answer 1

In M-Code for Power Query:
You will need to alter the two Source lines to reflect your actual Table names
Edited to allow for Open SKU's with no matching Inbound SKU's

let

//Read in the Two Tables
    Source = Excel.CurrentWorkbook(){[Name="OpenOrders"]}[Content],
    #"Open Orders" = Table.TransformColumnTypes(Source,{{"SKU", Int64.Type}, {"Open Orders", Int64.Type}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Inbound"]}[Content],
    #"Inbound Inventory" = Table.TransformColumnTypes(Source2,{
        {"SKU", Int64.Type}, {"Inbound Inventory", Int64.Type}, {"Available Date", type date}}),

//Join the tables
    #"Join tables" = Table.NestedJoin(#"Open Orders","SKU",#"Inbound Inventory","SKU", "Joined", JoinKind.LeftOuter),

//Add the computed columns
    #"Added Allocated Units" = Table.AddColumn(#"Join tables","Allocated Units", each 
        List.Min({[Open Orders], List.Sum([Joined][Inbound Inventory])??0}), Int64.Type),
    #"Added Remaining Units" = Table.AddColumn(#"Added Allocated Units","Remaining Units", each 
        List.Sum([Joined][Inbound Inventory])??0 - [Allocated Units]  , Int64.Type),
    #"Added Date" = Table.AddColumn(#"Added Remaining Units","Date", (r)=>
        List.First(
        List.Select(
            List.Generate(
                ()=>[a = r[Allocated Units] - r[Joined][Inbound Inventory]{0}, d= r[Joined][Available Date]{0}, idx=0],
                each [idx] < Table.RowCount(r[Joined]),
                each [a = [a] - r[Joined][Inbound Inventory]{[idx]+1}, d = r[Joined][Available Date]{[idx]+1}, idx=[idx]+1],
                each {[a],[d]}),
            each _{0}<=0)){1}?, type date),

//Remove the now unneeded "Joined" column
    #"Removed Columns" = Table.RemoveColumns(#"Added Date",{"Joined"})
in
    #"Removed Columns"