Recursive use of a column

• Question

• hi,

I have 2 tables: 1 PO with multiple items received from customers, and 1 table is current stock. It requires allocating the stock to each PO if the stock is available for a full PO (all PO items).

here is an example:

 PO Product Quantity PO1 a 10 PO1 b 20 PO2 a 3 PO2 b 5 PO3 a 3 PO3 b 3 PO4 a 2 PO4 b 5

Stock:

 Product Stock a 5 b 10

and the expected result:

 PO Product Quantity Allocated Stock Remained Stock PO1 a 10 0 5 PO1 b 20 0 10 PO2 a 3 3 2 PO2 b 5 5 5 PO3 a 3 0 2 PO3 b 3 0 5 PO4 a 2 2 0 PO4 b 5 5 0
 PO

I am stuck at the step of making recursively used the [Remained Stock] column, for the next step of calculating if PO having full stock or not.

Could you help a solution for this problem?

• Edited by Sunday, October 22, 2017 10:09 AM
Sunday, October 22, 2017 10:06 AM

• Yes, I was a bit in a hurry and didn't cover any variations in the data. Please find the new solution here (new link): https://www.dropbox.com/s/03o398x7iekhbef/TN_RecursiveStockAllocation2.xlsx?dl=0

and give it a thorough test with all possible data combinations :)

```// FinalNew
let
fnAllocation = (Current, Previous) =>
let
#"Merged Queries" = Table.NestedJoin(Current,{"Product"},Previous,{"Product"},"Stock1",JoinKind.FullOuter),
#"Removed Columns1" = Table.RemoveColumns(#"Merged Queries",{"Product"}),
#"Expanded Stock" = Table.ExpandTableColumn(#"Removed Columns1", "Stock1", {"RemainedStock", "Product"}, {"StockOld", "Product"}),
#"Replaced Value1" = Table.ReplaceValue(#"Expanded Stock",null,0,Replacer.ReplaceValue,{"Quantity"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "PreAllocate", each if [Quantity]<=[StockOld] then [Quantity] else 0),
in

Source = PO,
#"Grouped Rows" = Table.Group(Source, {"PO", "Product"}, {{"Quantity", each List.Sum([Quantity]), type number}}),
Partitioned = Table.Group(#"Grouped Rows", {"PO"}, {{"All", each _, type table}}),
ProductsFromPO = Table.Distinct(Table.SelectColumns(PO, {"Product"})),
AllProductsInPO = Table.AddColumn(ProductsFromPO, "Stock", each 0),
StartTable = Table.ExpandTableColumn(MergeWithStock, "Stock", {"Product", "Stock"}, {"Remained.Product", "Remained.Stock"}),
#"Removed Columns" = Table.RemoveColumns(StartTable,{"Product"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Remained.Product", "Product"}, {"Remained.Stock", "RemainedStock"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,0,Replacer.ReplaceValue,{"Quantity"}),

ListGenerate = List.Skip(List.Generate(()=>[Result = #"Replaced Value", Counter = 0],
Counter = [Counter]+1],
each [Result] )),
Combine = Table.Combine(ListGenerate)
in
Combine

```

Main differences to the previous solution:

• changed JoinType (to FullOuter) incl. adjustment of nulls
• adjustment of the stock-table that will include products who are in PO but not in Stock table
• aggregation of PO-items on Product per order to eliminate Product duplicates within one order

Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com

• Proposed as answer by Monday, October 23, 2017 1:20 PM
• Marked as answer by Monday, October 23, 2017 2:16 PM
Monday, October 23, 2017 8:01 AM

All replies

• What is the true scale of your requirement, i.e.:

1) Approximate number of products in total
2) No. of POs that need to be processed
3) Approximate maximum number of products per order.

Sunday, October 22, 2017 4:35 PM
• You can try this:

```let

fnStockAllocation = (tblStock, tblPO) =>
let
ListGenerate = List.Skip(List.Generate(()=> [StockStart = tblStock[Stock]{0}, Allocation = 0, RemainedStock= StockStart-Allocation, Counter = -1],
each [Counter] < Table.RowCount(tblPO),
each [  StockStart = [RemainedStock],
Allocation = if tblPO[Quantity]{Counter} <= StockStart then tblPO[Quantity]{Counter} else 0,
RemainedStock= StockStart-Allocation,
Counter = [Counter]+1])
),
Result = Table.FromRecords(ListGenerate),
MergeWithPO = Table.FromColumns(Table.ToColumns(tblPO)&{Result[Allocation]}&{Result[RemainedStock]}, Table.ColumnNames(tblPO)&{"Allocated Stock", "Remained Stock"})
in
MergeWithPO,

#"Grouped Rows" = Table.Group(#"Added Index", {"Product"}, {{"All", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Allocation", each fnStockAllocation(Table.Buffer(Table.SelectRows(Stock, (stock) => stock[Product]=[Product])), _[All])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Allocation"}),
#"Expanded Allocation" = Table.ExpandTableColumn(#"Removed Other Columns", "Allocation", {"PO", "Product", "Quantity", "Index", "Allocated Stock", "Remained Stock"}, {"PO", "Product", "Quantity", "Index", "Allocated Stock", "Remained Stock"}),
#"Sorted Rows" = Table.Sort(#"Expanded Allocation",{{"Index", Order.Ascending}})
in
#"Sorted Rows"```

But as Colin has indicated, performance might be an issue here.

If you don't need the original sort-order of your data, a shortened and faster version is this:

```let

fnStockAllocation = (tblStock, tblPO) =>
let
ListGenerate = List.Skip(List.Generate(()=> [StockStart = tblStock[Stock]{0}, Allocation = 0, RemainedStock= StockStart-Allocation, Counter = -1],
each [Counter] < Table.RowCount(tblPO),
each [  StockStart = [RemainedStock],
Allocation = if tblPO[Quantity]{Counter} <= StockStart then tblPO[Quantity]{Counter} else 0,
RemainedStock= StockStart-Allocation,
Counter = [Counter]+1])
),
Result = Table.FromRecords(ListGenerate),
MergeWithPO = Table.FromColumns(Table.ToColumns(tblPO)&{Result[Allocation]}&{Result[RemainedStock]}, Table.ColumnNames(tblPO)&{"Allocated Stock", "Remained Stock"})
in
MergeWithPO,
#"Grouped Rows" = Table.Group(PO, {"Product"}, {{"All", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Allocation", each fnStockAllocation(Table.Buffer(Table.SelectRows(Stock, (stock) => stock[Product]=[Product])), _[All])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Allocation"}),
#"Expanded Allocation" = Table.ExpandTableColumn(#"Removed Other Columns", "Allocation", {"PO", "Product", "Quantity", "Index", "Allocated Stock", "Remained Stock"}, {"PO", "Product", "Quantity", "Index", "Allocated Stock", "Remained Stock"})
in
#"Expanded Allocation"```

The "bolded" PO and Stock are the names of your respective queries. So if they have a different name in your model, you have to adjust the code accordingly.

Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com

Sunday, October 22, 2017 5:01 PM
• Hi Imke,

Your solution misses a crucial part of the requirement:

"It requires allocating the stock to each PO if the stock is available for a full PO (all PO items)". So if there are 10 of product a (instead of 5), your script allocates the 10 to PO1, even when stock for the full PO is not available (there are not enough of product b to fulfil PO1).

Sunday, October 22, 2017 6:08 PM
• Hi Colin,

The "Allocation"-row in the List-Generate function does this discrimination (to my understanding). Please have a look at the file:

https://www.dropbox.com/s/qz480qoc9kby8g5/TN_RecursiveStockAllocation1.xlsx?dl=0

Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com

Sunday, October 22, 2017 6:23 PM
• That's not happening. In your workbook, change the number of product a to 10 and refresh the query. You will see that 10 of product a is allocated to PO1, when 0 should be allocated, since there are not enough of product b to fulfil the PO.
Sunday, October 22, 2017 6:33 PM
• Now I understand what you mean, thank you - didn't get it before.

So you have a solution at hand already? Otherwise I'd modify my code.

Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com

Sunday, October 22, 2017 6:44 PM
• I haven't bothered looking for a solution mostly because I think that you already have a good start towards one. :)
Sunday, October 22, 2017 6:51 PM

```let
fnAllocation = (Current, Previous) =>
let
#"Merged Queries" = Table.NestedJoin(Current,{"Product"},Previous,{"Product"},"Stock1",JoinKind.LeftOuter),
#"Expanded Stock" = Table.ExpandTableColumn(#"Merged Queries", "Stock1", {"RemainedStock"}, {"StockOld"}),
#"Added Custom" = Table.AddColumn(#"Expanded Stock", "PreAllocate", each if [Quantity]<=[StockOld] then [Quantity] else 0),
in

Source = PO,
Partitioned = Table.Group(Source, {"PO"}, {{"All", each _, type table}}),
MergeWithStock = Table.NestedJoin(First,{"Product"},Stock,{"Product"},"Stock",JoinKind.LeftOuter),
StartTable = Table.ExpandTableColumn(MergeWithStock, "Stock", {"Stock"}, {"RemainedStock"}),
ListGenerate = List.Skip(List.Generate(()=>[Result = StartTable, Counter = 0],
Counter = [Counter]+1],
each [Result] )),
Combine = Table.Combine(ListGenerate)
in
Combine```
The file in the link is updated: https://www.dropbox.com/s/qz480qoc9kby8g5/TN_RecursiveStockAllocation1.xlsx?dl=0

Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com

Sunday, October 22, 2017 8:11 PM
• Hi Imke,

I think that the solution is too narrowly focused. It works fine with the example provided, but breaks down with a modest expansion of the tables. For example, I'm using the following tables:

 PO Product Quantity PO1 a 10 PO1 b 20 PO1 h 4 PO1 d 7 PO2 e 3 PO2 b 5 PO2 f 8 PO3 a 3 PO3 b 3 PO3 d 6 PO3 g 6 PO4 a 2 PO4 f 5 PO4 b 5

 Product Stock a 10 b 10 c 15 d 12 e 5 f 12 g 10 h 8

After PO1, many columns have null values, and most have error values.

Sunday, October 22, 2017 9:15 PM
• hi,

@Colin: I have ~1000 products, ~100 PO to be processed, and for each PO it has ~5 lines item (5 products)

@Imke: Thank you for your solution, it works well with the example. However, as per Colin pointed out, after the iteration of PO1, the stock table is updated to be the stock of only items existing in PO1, but it forgets the rest of stock items not in PO1.

Could you help updating the code to cover that scenario?

• Edited by Sunday, October 22, 2017 11:18 PM
Sunday, October 22, 2017 11:17 PM
• Yes, I was a bit in a hurry and didn't cover any variations in the data. Please find the new solution here (new link): https://www.dropbox.com/s/03o398x7iekhbef/TN_RecursiveStockAllocation2.xlsx?dl=0

and give it a thorough test with all possible data combinations :)

```// FinalNew
let
fnAllocation = (Current, Previous) =>
let
#"Merged Queries" = Table.NestedJoin(Current,{"Product"},Previous,{"Product"},"Stock1",JoinKind.FullOuter),
#"Removed Columns1" = Table.RemoveColumns(#"Merged Queries",{"Product"}),
#"Expanded Stock" = Table.ExpandTableColumn(#"Removed Columns1", "Stock1", {"RemainedStock", "Product"}, {"StockOld", "Product"}),
#"Replaced Value1" = Table.ReplaceValue(#"Expanded Stock",null,0,Replacer.ReplaceValue,{"Quantity"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "PreAllocate", each if [Quantity]<=[StockOld] then [Quantity] else 0),
in

Source = PO,
#"Grouped Rows" = Table.Group(Source, {"PO", "Product"}, {{"Quantity", each List.Sum([Quantity]), type number}}),
Partitioned = Table.Group(#"Grouped Rows", {"PO"}, {{"All", each _, type table}}),
ProductsFromPO = Table.Distinct(Table.SelectColumns(PO, {"Product"})),
AllProductsInPO = Table.AddColumn(ProductsFromPO, "Stock", each 0),
StartTable = Table.ExpandTableColumn(MergeWithStock, "Stock", {"Product", "Stock"}, {"Remained.Product", "Remained.Stock"}),
#"Removed Columns" = Table.RemoveColumns(StartTable,{"Product"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Remained.Product", "Product"}, {"Remained.Stock", "RemainedStock"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,0,Replacer.ReplaceValue,{"Quantity"}),

ListGenerate = List.Skip(List.Generate(()=>[Result = #"Replaced Value", Counter = 0],
Counter = [Counter]+1],
each [Result] )),
Combine = Table.Combine(ListGenerate)
in
Combine

```

Main differences to the previous solution:

• changed JoinType (to FullOuter) incl. adjustment of nulls
• adjustment of the stock-table that will include products who are in PO but not in Stock table
• aggregation of PO-items on Product per order to eliminate Product duplicates within one order

Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com

• Proposed as answer by Monday, October 23, 2017 1:20 PM
• Marked as answer by Monday, October 23, 2017 2:16 PM
Monday, October 23, 2017 8:01 AM
• Good work, Imke!

The final table contained several nulls in the PO column (created from the full outer join), but all looks fine after filtering out the nulls. The step to remove duplicates from the stock table is probably unnecessary - if the stock table isn't already unique, there is no way to know if the selected rows are the correct ones (there would have to be more columns e.g. a flag column with an "active" value, to make a clearer determination).

To determine whether the solution is viable in practice, it would be interesting to know how it performs with iamnvt's data.

Monday, October 23, 2017 1:41 PM
• Hello,

I've tested with data set of 200 PO, and 150 SKUs, each PO has 3-5 items.

It returns the result within ~ one minute.  This is acceptable from users.

Thanks to both for your solution, and discussion.

Monday, October 23, 2017 2:42 PM
• Hi All,

While Imke was working on her solution, I was having some thoughts on how I would approach this problem. I finally got around creating the following alternative solution. Although it may appear complex at first glance, the steps are, for the more part, straightforward.

Essentially, we first add the final required columns to the product table ("Allocated Stock" and "Remained Stock"). We then use List.Accumulate to iterate each PO, and again for each product in the PO. We decide on whether to fill the PO based on the difference between the current stock quantity and the PO quantity. Any negative value will result in not filling the PO. We use Table.ReplaceMatchingRows to update values in both the PO table and the stock table. The final result of updating the tables is returned as a record from the outer List.Accumulate.

In performance terms, it should be similar to Imke's solution.

```let
POTable = Table.Buffer(Excel.CurrentWorkbook(){[Name="PO"]}[Content]),
StockTable = Table.Buffer(Excel.CurrentWorkbook(){[Name="Stock"]}[Content]),
PONameList = POTable[PO],
AllocationAndBalance =
List.Accumulate(PONameList, [Stock = StockTable, PO = POTableAddedBalance], (accumulated, current) =>
let
FilteredPOTable = Table.SelectRows(POTableAddedBalance, each [PO] = current),
POProductList = FilteredPOTable[Product],
POQuantityList = FilteredPOTable[Quantity],
StockQuantityList = List.Transform(POProductList, (i) => Table.SelectRows(accumulated[Stock], each [Product] = i)[Stock]{0}),
TrialAllocation = List.Transform(List.Positions(POProductList), (i) => StockQuantityList{i} - POQuantityList{i}),
ConvertedNegativeToNulls = List.Transform(List.Positions(TrialAllocation), each if TrialAllocation{_} < 0 then null else TrialAllocation{_}),
Final = if List.Contains(ConvertedNegativeToNulls, null) then
let
Balance = accumulated[Stock],
Allocation = List.Accumulate(List.Positions(POProductList), accumulated[PO], (accumulated2, current2) =>
Table.ReplaceMatchingRows(
accumulated2,
{[PO = current, Product = POProductList{current2}, Quantity = POQuantityList{current2}, Allocated Stock = 0, Remained Stock = 0],
[PO = current, Product = POProductList{current2}, Quantity = POQuantityList{current2}, Allocated Stock = 0, Remained Stock = StockQuantityList{current2}]}
)
)
in
[Stock = Balance, PO = Allocation]
else
let
Balance = List.Accumulate(List.Positions(POProductList), accumulated[Stock], (accumulated2, current2) =>
Table.ReplaceMatchingRows(accumulated2, {[Product = POProductList{current2}, Stock = StockQuantityList{current2}], [Product = POProductList{current2}, Stock = TrialAllocation{current2}]})),
Allocation = List.Accumulate(List.Positions(POProductList), accumulated[PO], (accumulated2, current2) =>
Table.ReplaceMatchingRows(
accumulated2,
{[PO = current, Product = POProductList{current2}, Quantity = POQuantityList{current2}, Allocated Stock = 0, Remained Stock = 0],
[PO = current, Product = POProductList{current2}, Quantity = POQuantityList{current2}, Allocated Stock = POQuantityList{current2}, Remained Stock =TrialAllocation{current2}]}
)
)
in
[Stock = Balance, PO = Allocation]
in
Final
),
ResultPOTable = AllocationAndBalance[PO]
in
ResultPOTable```

Tuesday, October 24, 2017 7:33 PM
• Hi Colin,

I very much agree that List.Accumulate is much better here than List.Generate.

We can simplify the code even further which increases speed nicely:

```let

fnAllocation = (Previous, Current) =>
let
TryAllocation = Table.Combine({Table.AddColumn(Current, "RemainedStock", each -[Quantity]), Previous[RemStock]}),
#"Filled Down" = Table.FillDown(TryAllocation,{"PO"}),
ResultAllocation = Table.Group(#"Filled Down", {"Product", "PO"}, {{"Quantity", each List.Sum([Quantity]), type number}, {"RemainedStock", each List.Sum([RemainedStock]), type number}, {"Allocate", each List.Sum([Quantity]), type number}}),
Delivery = if List.Min(ResultAllocation[RemainedStock])<0 then
[Delivery = Table.Combine({Table.Join(Current, "Product", Previous[RemStock], "Product"), Previous[Delivery]}),
RemStock = Previous[RemStock]
]
else
[Delivery = Table.SelectRows(Table.Combine({ResultAllocation, Previous[Delivery]}), each [Quantity]<>null),
RemStock = Table.SelectColumns(ResultAllocation, {"Product", "RemainedStock"})
]
in
Delivery ,

PO = Table.Buffer(Excel.CurrentWorkbook(){[Name="PO"]}[Content]),
Stock = Table.Buffer(Table.RenameColumns(Excel.CurrentWorkbook(){[Name="Stock"]}[Content], {{"Stock", "RemainedStock"}})),
Partitioned = Table.Group(PO, {"PO"}, {{"All", each _, type table}}),
ListAccumulate = List.Accumulate(Partitioned[All], [Delivery= #table({}, {{}}), RemStock = Stock], (Previous, Current) => fnAllocation(Previous, Current))[Delivery]
in
ListAccumulate```

@iamnvt: Does this improve performance with your data as well?

Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com

Wednesday, October 25, 2017 5:26 PM
• Hi Imke, List.Accumulate is the best for-each loop function in M. I now use List.Generate only when I want to execute a do/while loop. I have seen no performance difference between doing the same task in List.Accumulate vs. List.Generate, but I've done this comparison in only a couple of projects.

Your new code looks very good!

Wednesday, October 25, 2017 6:46 PM
• Hi all,

This post was really helpful as I was trying to do something very similar. List.Accumulate worked really well. However, while it worked on a smaller sample dataset (~100 to 1,000 rows) while I was testing it, I'm now having problems with my actual data (which is ~5,000 to 10,000 rows). I get a "Expression.Error: Evaluation resulted in a stack overflow and cannot continue" when I try to run it on the larger dataset. Is this a limitation of List.Accumulate? Any ideas or tips to debug?

I'm happy to share my code, but thought I would see if it's a known issue before diving in...

Thanks!

Cory
Thursday, October 31, 2019 7:13 PM