Recursion problem using power query

Power Query
1

Is there any better way of implementing recursion than this method?
The method above is working fine, but for large set of data (even 10000 rows) it is taking lot of time.

2

Hi @Anu,

Can you test if Buffering your data improves performance?
Example below.

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], BufferedData = Table.Buffer(SourceTyped),
      Result = List.Accumulate(
        Table.SelectRows(BufferedData, each ([Date]<= currDate and [Item] = currItem))[StockMovement]
        , 0
        , (state, current) => List.Max({state+current,0}) 
      )
    in Result
))
in
    CalcProvStock
1 Like
4

Can it be improved any further?

5

We’ve noticed that you posted on an inactive and previously tagged solved topic. For more visibility please start a new topic within the forum. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951