The Goal: Calculate the running demand by product as listed by customer desired date to know which orders can ship and at what point (future date) the on-hand inventory will not support open orders.
The Data: A Microsoft query against a Microsoft SQL DB returns a MS Excel table with orders by line item and starting quantity on hand.
An order could be a mix and match of any 900 different products in any quantity the customer desired.
Data is first in first out (sorted by desired date). With a daily query refresh, complete order numbers or specific line items will be fulfilled and drop out of the table. As new orders arrive, they appear in the table (at the bottom). Overall, 900 products with approximately 1100 open line items at any time.
ORDER # | DESIRED DATE| PRODUCT | QTY DUE | REM ON HAND | Cum Dmd
CO-32 | 1/3/19 | Ducks | 2 | 5 | 100 (all open orders)
CO-32 | 1/3/19 | Chick | 100 | 3 | 100
CO-33 | 1/7/19 | Ducks | 2 | 3 | 102
CO-33 | 1/7/19 | CatsA | 5 | 15| 10
CO-33 | 1/8/19 | Chick | 10 | -7 | 110
CO-34 | 1/8/19 | CatsA | 2 | 13 | 12
CO-35 | 2/10/19| DogsA | 25 | 100 | 125
CO-35 | 2/10/19| Ducks | 25 | -22 | 127
Current Problem:
(A) The resulting Power Query code below results with a new tab, new table that misses rows - only returns about 677 rows of original 1105 line item orders.
(B) Code in query will lockup client processor for 4-5 minutes to loop through and the data set..
Current code:
let
Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PART", type text}, {"QTY DUE", Int64.Type}}),
AddIndex = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
RunTotal = Table.AddColumn(AddIndex, "Cum Dmd", each let PART=[PART], Index=[Index]
in
List.Sum(Table.SelectRows(AddIndex,
each [Index]<=Index and [PART]=PART)[QTY DUE])),
#"Removed Columns" = Table.RemoveColumns(RunTotal,{"Index"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Remain QOH", each [T ON HAND]-[Cum Dmd]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"ORDER", "CUSTOMER", "PART", "MISC_REFERENCE", "QTY DUE", "Remain QOH", "T ON HAND", "T Demand", "ORDER QTY", "SHIPPED QTY", "Customer DESIRE", "PROMISE", "Order Date", "ENTERED_BY", "Age", "Cum Dmd"})
in
#"Reordered Columns"