1

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"

enter image description here

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • I think one of the causes for the long processing time might be that this part of the code `...Table.SelectRows(AddIndex, each [Index]<=Index and [PART]=PART)...` seems to look at every row in `AddIndex` table for each row of your current table. Assuming column `Index` is 1-based (which appears to be the case), you might want to instead try something like `...Table.SelectRows(Table.FirstN(AddedIndex, [Index]), each [PART] = PART)...` which only looks at the first `N` rows of `AddIndex` table for each row of your current table (where `N` is a 1-based index). – chillin Jan 05 '19 at 16:27
  • However, what's inefficient about both approaches in my previous comment is that each row in the current table "ignores" all the work done in the previous row, and starts processing `AddedIndex` from scratch i.e. the first row. Something like `List.Accumulate` whilst looping over the table (as a list of records) and maintaining the cumulative total for each product in a separate set of `Records` might be a reasonably efficient, as it only requires one pass over `AddIndex` (and not one for every row in the current table). I would answer, but your question/desired output is unclear (to me). – chillin Jan 05 '19 at 16:35
  • Chillin - thank you for the reply. My desired output is the cumulative demand (Cum dmd) for the product to be tallied and shown in every every row in the table. With that, we can compete the math by subtracting this from the on-hand amount. A nice second desired result would be to accomplish this calculation in the most efficient method. – John M - Fanblade Jan 06 '19 at 04:18

0 Answers0