Power Query: Assign stock to orders

[batteredveg]

Hi experts,

I'm trying to use Power Query to assist with assigning stock to orders. I have two tables, an Orders table (sorted in order priority) and a Stock table.

Orders

OrderID	StockID	Priority	QtyRequired
1030	10001001	1	4
1031	10001002	1	2
1032	10001002	2	2
1033	10001001	3	2
1034	10001002	3	2

Stock

StockID	StockQty
10001001	5
10001002	3

I ideally want to replicate the Orders table and add the available stock for each item to each row (deducting any stock assigned to other orders, returning zero where there is no stock left), so I would end up with something like this:

OrderID	StockID	Priority	QtyRequired	StockAvailable
1030	10001001	1	4	4
1031	10001002	1	2	2
1032	10001002	2	2	1
1033	10001001	3	2	1
1034	10001002	3	2	0

I've tried creating this in Power Query, but don't even have a partial success to share. Merging queries is straightforward, but it's the calculating stock used against each order that's alluding me.

Any help would be much appreciated!

P.

 

[JGordon11]

let
    Orders = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Stock = Excel.CurrentWorkbook(){[Name="Stock"]}[Content],
    tbl = Table.AddIndexColumn(Orders,"Index"),
    tbl1 = Table.NestedJoin(tbl, {"StockID"}, Stock, {"StockID"}, "tbl", JoinKind.LeftOuter),
    tbl2 = Table.ExpandTableColumn(tbl1, "tbl", {"StockQty"}),
    tcn = Table.ColumnNames(tbl2),
    tbl3 = Table.Group(tbl2, {"StockID"}, {{"All", each _}}),
    tbl4 = Table.AddColumn(tbl3, "Available", each 
                List.Skip(List.Accumulate(List.Zip({[All][QtyRequired], [All][StockQty]}), {0}, (s,c)=> 
                        s & {List.Min({c{1} - List.Sum(s), c{0}}) }))),
    tbl5 = Table.AddColumn(tbl4, "Combined", each Table.FromColumns(Table.ToColumns([All]) & {[Available]}, tcn & {"Available"})),
    tbl6 = Table.SelectColumns(tbl5,{"Combined"}),
    tbl7 = Table.ExpandTableColumn(tbl6, "Combined", {"Index", "OrderID", "StockID", "Priority", "QtyRequired", "Available"}),
    tbl8 = Table.Sort(tbl7,{{"Index", Order.Ascending}}),
    Result = Table.RemoveColumns(tbl8,{"Index"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	OrderID	StockID	Priority	QtyRequired		OrderID	StockID	Priority	QtyRequired	Available
2	1030	10001001	1	4		1030	10001001	1	4	4
3	1031	10001002	1	2		1031	10001002	1	2	2
4	1032	10001002	2	2		1032	10001002	2	2	1
5	1033	10001001	3	2		1033	10001001	3	2	1
6	1034	10001002	3	2		1034	10001002	3	2	0
7
8	StockID	StockQty
9	10001001	5
10	10001002	3
11
Sheet3

 

[batteredveg]

let
    Orders = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Stock = Excel.CurrentWorkbook(){[Name="Stock"]}[Content],
    tbl = Table.AddIndexColumn(Orders,"Index"),
    tbl1 = Table.NestedJoin(tbl, {"StockID"}, Stock, {"StockID"}, "tbl", JoinKind.LeftOuter),
    tbl2 = Table.ExpandTableColumn(tbl1, "tbl", {"StockQty"}),
    tcn = Table.ColumnNames(tbl2),
    tbl3 = Table.Group(tbl2, {"StockID"}, {{"All", each _}}),
    tbl4 = Table.AddColumn(tbl3, "Available", each
                List.Skip(List.Accumulate(List.Zip({[All][QtyRequired], [All][StockQty]}), {0}, (s,c)=>
                        s & {List.Min({c{1} - List.Sum(s), c{0}}) }))),
    tbl5 = Table.AddColumn(tbl4, "Combined", each Table.FromColumns(Table.ToColumns([All]) & {[Available]}, tcn & {"Available"})),
    tbl6 = Table.SelectColumns(tbl5,{"Combined"}),
    tbl7 = Table.ExpandTableColumn(tbl6, "Combined", {"Index", "OrderID", "StockID", "Priority", "QtyRequired", "Available"}),
    tbl8 = Table.Sort(tbl7,{{"Index", Order.Ascending}}),
    Result = Table.RemoveColumns(tbl8,{"Index"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	OrderID	StockID	Priority	QtyRequired		OrderID	StockID	Priority	QtyRequired	Available
2	1030	10001001	1	4		1030	10001001	1	4	4
3	1031	10001002	1	2		1031	10001002	1	2	2
4	1032	10001002	2	2		1032	10001002	2	2	1
5	1033	10001001	3	2		1033	10001001	3	2	1
6	1034	10001002	3	2		1034	10001002	3	2	0
7
8	StockID	StockQty
9	10001001	5
10	10001002	3
11
Sheet3

Thank you so much - this is exactly what I needed.

Best wishes,
P