Recursion problem using power query

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.

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