Power Query: Assign stock to orders

batteredveg

New Member
Joined
Aug 27, 2014
Messages
7
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

OrderIDStockIDPriorityQtyRequired
10301000100114
10311000100212
10321000100222
10331000100132
10341000100232

Stock

StockIDStockQty
100010015
100010023

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:

OrderIDStockIDPriorityQtyRequiredStockAvailable
103010001001144
103110001002122
103210001002221
103310001001321
103410001002320

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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Power Query:
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
ABCDEFGHIJK
1OrderIDStockIDPriorityQtyRequiredOrderIDStockIDPriorityQtyRequiredAvailable
210301000100114103010001001144
310311000100212103110001002122
410321000100222103210001002221
510331000100132103310001001321
610341000100232103410001002320
7
8StockIDStockQty
9100010015
10100010023
11
Sheet3
 
Upvote 3
Solution
Power Query:
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
ABCDEFGHIJK
1OrderIDStockIDPriorityQtyRequiredOrderIDStockIDPriorityQtyRequiredAvailable
210301000100114103010001001144
310311000100212103110001002122
410321000100222103210001002221
510331000100132103310001001321
610341000100232103410001002320
7
8StockIDStockQty
9100010015
10100010023
11
Sheet3
Thank you so much - this is exactly what I needed.

Best wishes,
P
 
Upvote 0

Forum statistics

Threads
1,223,377
Messages
6,171,743
Members
452,419
Latest member
mapa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top