When you search the web for solutions to reference previous or next rows in the query editor of Power BI or Power Query, you will find many solutions that base on an added index-column. But the problem with these solutions on large tables is that performance will range between slow and extremely slow. In this post I’ll show a faster method with function Table.ReferenceDifferentRow .

Basic mechanism

This new mechanism doesn’t use an index that is either used to merge the table with itself or to be referenced as a new row index. Instead, I “simply” add one table next to the other. To retrieve the previous row from a table, I reference the original table, delete its last row and add a blank row on top. That will “shift” the first row to the second row. Then I “put” this table just right to the original table, without referencing a key or applying any sort of logic. This will speed up the process considerably:

Shift table to reference different rows

The key of this method is the Table.FromColumns-function: It creates a table from a list of columns in the order of the columns in the list. So I just have to find a way to turn 2 tables into 1 list of columns:

Table.ToColumns(OriginalTable) & Table.ToColumns(ShiftedTable)

will do this job. Table.ToColumns will turn a table into a list of columns and the ampersand (“&”) will concatenate the lists from both tables.

The functions

The basic function that references the previous row of a column looks like so:

Reference your table in the 1st parameter and the give the column name whose previous row you want to see as the 2nd parameter.

But I’ve also included this basic mechanism into a handy function with some bells and whistles: “Table.ReferenceDifferentRow” Please be aware, that this function is not as fast as the main simple function!

How the Table.DifferentRow-function works

1. The only mandatory parameter is your table and then it will return a table with the previous rows values of all columns. So Table.ReferenceDifferentRow(MyTable) will return the result from above.
2. The default-value for this parameter is set to -1 to return the previous row if you omit it. If you want the values from the next row instead, fill in 1. 2 will return the overnext and -2 the pre-previous row. This is what Table.ReferenceDifferentRow(MyTable, -2) returns:
   

   -2 will return the pre-previous row

    

3. You probably just need one or a few columns/fields from the previous row: In the 3rd parameter you can enter a list of column names to be returned:   Table.ReferenceDifferentRow(MyTable, null, {"Value"}):
   

   Select specific columns

    

4. Quite often the previous values shall only be returned within a group of rows. (That’s when you use [MyColumn] = EARLIER([MyColumn]) in DAX). You can enter a list of group-columns in the 4th parameter of this function: Table.ReferenceDifferentRow(MyTable, null, null, {"Product"})
   

   Group by columns

5. By default, the suffix “.Prev” will be added to the new column names. Use the 5th parameter to change if needed. In this example, I reference the row below using “1” for the 2nd parameter: Table.ReferenceDifferentRow(MyTable, 1, null, null, "Next")
   

   Changing suffix and referencing next row

6. If performance is still too bad, you can try to buffer the input table. Any value in the 6th parameter will do that for you (although I haven’t seen a performance improvement for my test cases).

Why not use DAX?

Referencing a previous row in DAX is still faster than my method above. So if you can do it in DAX, go ahead (create an index in the query editor and use LOOKUPVALUE like shown here: https://powerpivotpro.com/2015/03/how-to-compare-the-current-row-to-the-previous-row-in-power-pivot/. ) My function is for cases where you have to stay in M.

Enjoy & stay queryious 😉