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

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: ) My function is for cases where you have to stay in M.

Enjoy & stay queryious 😉

Comments (13) Write a comment

  1. How can I call a custom function based on the name, Table.ReferenceDifferentRow? In Excel 2016, it does not allow query names to contain periods, so if I try to name the function Table.ReferenceDifferentRow, I get an error. I would like to have it so I can call the function just like your example code:

    Table.ReferenceDifferentRow( #table( {“Product”, “Value”}, List.Zip( { {“A” ,”A” ,”B” ,”B” ,”B”}, {“1″ ,”2″ ,”3″ ,”4″ ,”5”} } ) ) )

    Instead, I would need to name it something like fnTableReferenceDifferentRow, but I would like to know if I can name my functions with Table.MyFunctionName so they look more like the built in function names.



  2. Hi Imke,

    I’m getting the following error:
    An error occurred in the ‘’ query. Expression.Error: We cannot apply operator & to types Text and List.

    “ID” is the name of my Group By column. I’ve tried changing the data type for that column but same error is returned.


    • Could it be that you’ve passed the “ID” as the 3rd argument instead or forgot to pass it on as a list (so wrap it into curly brackets) like so?:

      Table.ReferenceDifferentRow(MyTable, null, null, {"ID"})


  3. Pingback: Unravel cumulative totals to their initial elements in Power BI and Power Query – The BIccountant

  4. @Imke – This is a super useful function

    Here is another way – which uses the same method that you have described but does not have the same Flexibility as your function
    If D is the name of a table

    then the below produces an offset table by 1 row

    Source = Table.FromColumns(Table.ToColumns(D) & Table.ToColumns(Table.RemoveLastN(Table.Combine({Table.PromoteHeaders(Table.FromColumns(List.Zip({Table.ColumnNames(D), List.Repeat({null},Table.ColumnCount(D))}))),D}),1)))


  5. Hi Imke,

    Interesting…What you’re saying is that converting the table to lists, doing some manipulation and converting the lists back to a table is faster than adding an index column to a table, and adding a custom column where each row references the index column. It never occurred to me that such might be the case.

    Anyway, inspired by your post, I created the following function, which creates a list with the values offset from another column, and adds the list as a new column in Table.FromColumns. I call the function Table_OffsetColumnValues.

    (table as table, columnToOffset as text, newColumnName as text, optional offsetDirection as text) =>
    Table = Table.Buffer(table),
    OffsetDirection = Text.Upper(offsetDirection),
    ValidOffsetDirections = {“UP”, “DOWN”, null},
    OffsetColumnValues = if OffsetDirection = “DOWN” or OffsetDirection = null then
    {null} & List.Buffer(List.RemoveLastN(Table.Column(Table, columnToOffset)))
    else List.Buffer(List.RemoveFirstN(Table.Column(Table, columnToOffset))) & {null},
    NewTable = if List.Contains(ValidOffsetDirections, OffsetDirection) then
    Table.FromColumns(Table.ToColumns(Table) & {OffsetColumnValues}, Table.ColumnNames(Table) & {newColumnName})
    else error “Offset direction must be Up or Down – any case accepted.”


    • Thanks Colin,
      Yes, I was surprised as well, but the difference on large tables is really significant.

      Cool function & nice implementation of the error-messages 😉


  6. Pingback: Writing data to GitHub using Power Query only – The BIccountant

  7. Hi Imke,

    This is an incredibly helpful function and I was so excited to use it… but what was working with my test data is timing out when I connect to a live data source. I am only using a subset of my data so the number of rows is very small; can you think of what may be different? My code is below; I started with your function but as my parameters were static, I hard-coded them. Also, I needed three shifts in the data:

    = Table.FromColumns(
    Table.ToColumns(SourceTable) &
    Table.RemoveFirstN(Table.SelectColumns(SourceTable, “Value”),1)
    & #table({ “Value” }, {{null}})
    ) &
    Table.RemoveFirstN(Table.SelectColumns(SourceTable, “Value”),2)
    & #table({ “Value” }, {{null}})
    ) &
    Table.RemoveFirstN(Table.SelectColumns(SourceTable, “Value”),3)
    & #table({ “Value” }, {{null}})
    Table.ColumnNames(SourceTable) &
    List.Transform({ “Value” }, each _&”A”) &
    List.Transform({ “Value” }, each _&”B”) &
    List.Transform({ “Value” }, each _&”C”)

    Thank you for any insight!


    • Hi Bekah,
      I must admit that I cannot find a resemblance of your code with mine.
      My function can do many variations of previous rows in it’s current shape. So if you don’t want the previous row, but the pre-pre-previous row instead, you set the second parameter to -3.


      • Hi Imke,

        I actually need all four values on each row (the original and the three rows below it):

        1 2 3 4
        2 3 4 5
        3 4 5 null
        4 5 null null
        5 null null null

        So I have taken your code and repeated it a few times. My needs are always static so I did not create all of the parameters (the values will always be the same). I simply took the main code, inserted the hard-coded parameters and applied it. It works perfectly with my sample data but can not handle larger datasets. I wonder if this is because of the repetition?

        Thanks again for the well thought out process!


Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: