Recursion problem

Power Query
1

I am trying to implement the same logic , but I am not able to get the output as shown.
Can somebody help?Prov stock2_10.pbix (158.5 KB)

2

Hi @Anu,

The issue was a type mismatch.
Your file, had the Date formatted as Text

image

Once changed to a valid Date data type, the applied logic works as expected.

image

I hope this is helplful

3

image

From the above step,Do I need to expand the AdjProvStock Column? To get thisimage

4

Hi @Anu,

each of the nested tables contain the same data, so you might want to:

  1. extract the data from the same row from the nested table OR
  2. downdrill into one of the nested table objects

If you choose option 1
Add an Index column from 0 and add a custom column with this logic (shown inside the formula bar)

image
image878×256 18.4 KB

or for option 2
Right click next to any of the nested tables and select “Drill Down”
image

I hope this is helpful

2 Likes
5

If you don’t mind could you please share the file that you have done

6

Sure, no problem!

Both methods are implemented in this version of your file.

eDNA Prov stock2_10.pbix (162.3 KB)

7

One more question, if you would be so kind to check. I promise this will be the last on this thread

image
image724×426 14.8 KB

Suppose if I had two items in the source, Item A and B and the recursion logic has to be updated,
How can I edit the query to include it?
I mean the AdjProvStock has to be checked seperately for each item.

8

Hi @Anu,

Just add a currItem variable and refer to that in the Table.SelectRows filter condition, like below.
You can copy and paste this code into a new blank query

let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc6xDcAgDETRXVyDwAaUUMZrIPZfA1IQLpJpKJ7Q+bdGEnKQyJUccZzPQ929yrz4Qr2XesHfUpbXT9NeFsA97OFgwukMvJcL3Ps3q9msh2Y1m9Vq1kOz2s1qNk/tAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, StockMovement = _t, Item = _t]),
        SourceTyped = Table.TransformColumnTypes(Source,{{"Date", type date}, {"StockMovement", Int64.Type}}),
        CalcProvStock = Table.AddColumn(SourceTyped, "AdjProvStock", each Table.AddColumn(SourceTyped, "AdjProvStock", each 
        let currDate = [Date], currItem = [Item],
          Result = List.Accumulate(
            Table.SelectRows(SourceTyped, each ([Date]<= currDate and [Item] = currItem))[StockMovement]
            , 0
            , (state, current) => List.Max({state+current,0}) 
          )
        in Result
    ))
    in
        CalcProvStock

.

I hope this is helpful.

1 Like