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 function

I’ve included this basic mechanism into a handy function with some bells and whistles: “Table.ReferenceDifferentRow”

 

How it 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 😉

Comments (6) 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.

    Thanks

    Reply

  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.
    Details:
    Operator=&
    Left=ID
    Right=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.

    Reply

    • 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"})

      Reply

  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

    let
    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)))
    in
    Source

    Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz