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
Stock
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:
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.
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.