Power BI M-QUERY

[ArvindJha]

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 = if [SUBID] = [PreviousSUBID] and [TRANID] = [PreviousTRANID] then [Index] - 2 else [Index] - 1,
PrevValue = try if PrevIndex >= 0 then Source{PrevIndex}[FORMTIME] else null otherwise null
in
PrevValue

[v-cgao-msft]

Hi @ArvindJha ,

How did you get the data (subid, tranid..) from the previous row? Please compare the performance of these two methods.

Fast and easy way to reference previous or next rows in Power Query or Power BI –

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[danextian]

hI @ArvindJha 

If scanning a table, you're better off using DAX.

Option 1 - Load the table with Index and use EARLIER to access the previous row or rows

Calc column =
VAR _row =
    IF (
        data[subid] = data[previoussubid]
            && data[tranid] = data[previoustranid],
        2,
        1
    )
RETURN
    CALCULATE (
        MAX ( data[value] ),
        FILTER ( ALL ( data ), data[index] = EARLIER ( data[index] ) - _row )
    )

Option2 - Reference the query, keep the index column and the needed column/s. Load both queries and use LOOKUPVALUE.

Calc column =
--in the original query
VAR _row =
    IF (
        data[subid] = data[previoussubid]
            && data[tranid] = data[previoustranid],
        2,
        1
    )
RETURN
    LOOKUPVALUE ( data2[value], data2[index], data[index] - _row )

[ArvindJha]

Thanks for the response , the reason i cannot use DAX i need to do multiple additional transformatioms with the output along with multiple joins

[danextian]

So there is this mantra - ETL to be done as upstream as possible as downstream as necessary. While M can certainly do, there are just things that M isn't optimized for and that includes scanning a table. 

This another approach in M. It is probably faster but I don't think any faster.

Reference your original query twice. 
Query A keeps all the columns but with another one for the PrevIndex:

PrevIndex = if [SUBID] = [PreviousSUBID] and [TRANID] = [PreviousTRANID] then [Index] - 2 else [Index] - 1
Query B keeps the index and the necessary columns only.

Merge Query A with Query B on PrevIndex and Index respectively.

[bhanu_gautam]

@ArvindJha , 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

[ArvindJha]

thanks for the response , the performance is still slow , was using buffer earlier as well Table.buffer