Forum Discussion
ArvindJha
Helper III
Helper IIIPower BI M-QUERY
Hello Team, How to optimize below m-query , it takes lot of time to load in hours , the logic is to fetch data from two rows back if the condition is met else from one row back: let PrevIndex =...
bhanu_gautam
Super User
Super UserArvindJha , Try using List.buffer
let
// Load your source data
Source = ...,
// Buffer the source table to improve performance
BufferedSource = List.Buffer(Source),
// Add a custom column to calculate the previous value
AddPrevValueColumn = Table.AddColumn(BufferedSource, "PrevValue", each
let
CurrentIndex = [Index],
PrevIndex = if [SUBID] = [PreviousSUBID] and [TRANID] = [PreviousTRANID] then CurrentIndex - 2 else CurrentIndex - 1,
PrevValue = try if PrevIndex >= 0 then BufferedSource{PrevIndex}[FORMTIME] else null otherwise null
in
PrevValue
)
in
AddPrevValueColumn
ArvindJhathanks for the response , the performance is still slow , was using buffer earlier as well Table.buffer