Latest Enterprise DNA Initiatives

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

Can it be improved any further?

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 How To Use The Enterprise DNA Support Forum