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 .

Edit 9th November 2021: The index-method in Power Query has improved since writing this article. And while for my tests, my solution is still faster, the gap has narrowed and I heard from applications where the index method was actually faster. So I strongly recommend that you compare both versions for your specific use case.
It looks, as if the wider the tables are, the Index-Method (merging) will become faster.

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:


let func =
(MyTable as table, MyColumnName as text) =>
let
Source = MyTable,
ShiftedList = {null} & List.RemoveLastN(Table.Column(Source, MyColumnName),1),
Custom1 = Table.ToColumns(Source) & {ShiftedList},
Custom2 = Table.FromColumns(Custom1, Table.ColumnNames(Source) & {"Previous Row"})
in
Custom2 ,
documentation = [
Documentation.Name = " Table.PreviousRow ",
Documentation.Description = " Superfast way to reference previous row ",
Documentation.LongDescription = " Superfast way to reference previous row ",
Documentation.Category = " Table ",
Documentation.Source = " www.TheBIccountant.com . http://tiny.cc/hhus5y . ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. http://tiny.cc/hhus5y . ",
Documentation.Examples = {[Description = " ",
Code = " ",
Result = " "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

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!


let func =
(Table as table, optional Step as number, optional SelectedColumns, optional GroupByColumns, optional Suffix as text, optional Buffer as any) =>
let
// Steps to prepare the (optional) parameters for the nested function "fnFetchNextRow"
Source = if Buffer = null then Table else Table.Buffer(Table),
Step0 = if Step = null then -1 else Step,
Step_ = if Step = null then 1 else Number.Abs(Step),
Suffix = if Suffix = null then ".Prev" else Suffix,
GroupByColumns = if GroupByColumns = null then null else GroupByColumns,
ShiftFunction = if Step0 < 0 then Table.RemoveLastN else Table.RemoveFirstN,
ColNames = List.Buffer(Table.ColumnNames(Source)),
NewColNames = if SelectedColumns = null then ColNames else SelectedColumns,
CountNewCols = List.Count(NewColNames),
// Core function that retrieves values from previous or next rows (depending on sign of parameter "Step")
fnFetchNextRow = (Table_ as table, optional Step as number, optional SelectedColumns, optional Suffix as text, optional Buffer as any) =>
let
MergeTable = if SelectedColumns = null then Table_ else Table.SelectColumns(Table_, SelectedColumns),
Shift = if Step0 > 0 then ShiftFunction(MergeTable, Step_) & #table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_))
else #table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_)) & ShiftFunction(MergeTable, Step_),
Reassemble = Table.ToColumns(Table_) & Table.ToColumns(Shift),
Custom1 = Table.FromColumns( Reassemble, Table.ColumnNames(Source) & List.Transform(NewColNames, each _&Suffix ) )
in
Custom1,
// optional grouping on certain columns
#"Grouped Rows" = Table.Group(Source, GroupByColumns, {{"All", each _}}, GroupKind.Local),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fnFetchNextRow([All], Step0, SelectedColumns, Suffix, Buffer)),
#"Removed Columns" = Table.Combine(Table.RemoveColumns(#"Added Custom", GroupByColumns & {"All"})[Custom]),
// case no grouping
NoGroup = fnFetchNextRow(Source, Step0, SelectedColumns, Suffix, Buffer),
// select case grouping
Result = if GroupByColumns = null then NoGroup else #"Removed Columns"
in
Result ,
documentation = [
Documentation.Name = " Table.ReferenceDifferentRow ",
Documentation.Description = " Adds columns to a <code>Table</code> with values from previous or next rows (according to the <code>Step</code>-index in the 2nd parameter) ",
Documentation.LongDescription = " Adds columns to a <code>Table</code> with values from previous or next rows (according to the <code>Step</code>-index in the 2nd parameter) ",
Documentation.Category = " Table ",
Documentation.Source = " ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann (www.TheBIccountant.com ) ",
Documentation.Examples = {[Description = " ",
Code = " fnTable_ReferenceDifferentRow( #table( {""Product"", ""Value""}, List.Zip( { {""A"" ,""A"" ,""B"" ,""B"" ,""B""}, {""1"" ,""2"" ,""3"" ,""4"" ,""5""} } ) ) ) ",
Result = " #table( {""Product"", ""Value"", ""Product.Prev"", ""Value.Prev""}, List.Zip( { {""A"" ,""A"" ,""B"" ,""B"" ,""B""}, {""1"" ,""2"" ,""3"" ,""4"" ,""5""}, {null ,""A"" ,""A"" ,""B"" ,""B""}, {null ,""1"" ,""2"" ,""3"" ,""4""} } ) ) "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

 

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 fnTable_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 fnTable_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:   fnTable_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: fnTable_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: fnTable_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).

How to apply the function

Please check out this short video on how to apply the function

 

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 (56) 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

  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) =>
    let
    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.”
    in
    NewTable

    Reply

    • 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 😉

      Reply

  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.ToColumns(
    Table.RemoveFirstN(Table.SelectColumns(SourceTable, “Value”),1)
    & #table({ “Value” }, {{null}})
    ) &
    Table.ToColumns(
    Table.RemoveFirstN(Table.SelectColumns(SourceTable, “Value”),2)
    & #table({ “Value” }, {{null}})
    ) &
    Table.ToColumns(
    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!

    Reply

    • 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.
      /Imke

      Reply

      • 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!

        Reply

  8. Very interesting solution that I think gets me close to what I am looking for. I have a more complicated file because the source system creates multiple the data in a non-repeating fashion.

    Example

    Season, KCA – November 2019
    Class, KCA Select Friday 4:30pm
    ,Customer, Age, Email, Tel
    ,Jon Doe,12,xzy@abc.com,xxx-xxx-xxxx
    ,Jane Smith, 13,abc@xby.com,xxx-xxx-xxxx
    Class, KCA Select Friday 5:45pm
    ,Customer, Age, Email, Tel
    ,Jon Doe,12,xzy@abc.com,xxx-xxx-xxxx
    ,Jane Smith, 13,abc@xby.com,xxx-xxx-xxxx

    What I would like to get as my end result is
    Class Month, Class Year, Class Day, Class Time, Customer, Age, Email, Tel
    November, 2019,Friday, 4:30,Jon Doe,12,xzy@abc.com,xxx-xxx-xxxx
    November, 2019,Friday, 4:30,Jane Smith, 13,abc@xby.com,xxx-xxx-xxxx
    November, 2019,Friday, 5:45,Jon Doe,12,xzy@abc.com,xxx-xxx-xxxx
    November, 2019,Friday, 5:45,Jane Smith, 13,abc@xby.com,xxx-xxx-xxxx

    Using your technique I know I can get the Class Month and Class Year. The problem I see is creating the Class Day and Time given they change as the file is processed. The long term challenge is we would want this to accept new files each month so we could start doing trend analysis.

    Any advice would be appreciated.

    Reply

  9. Hi There,

    I would like to ask how to apply this method during the middle of a long query. It is explained here to reference the Table at its Source form, but at many instances we need to get the next row value after applying many steps to the source data (e.g. applied some filters or Sorted columns etc). How can we reference the Table at the current state not as source state. I hope the explanation is clear.

    Reply

  10. Thank you so much for this, I have read multiple articles purporting to do something similar to this, and every piece of code has failed or been full of bugs.

    You have nailed it perfectly with an easy to follow guide.

    Reply

  11. Pingback: Covid-19 dashboard with Power Query / Power Pivot / Excel

  12. I am having issues trying to invoke this function from this state:

    Product Value Previous Value as New Column
    A 1 null
    A 2 1
    B 3 2
    B 4 3
    B 5 4

    When prompted for the table name, I’m stuck. I want to use the current table but that is not an option. I am trying to invoke with the Add Column / Invoke Custom Function in the UI.

    If not the table I want to invoke in is not open, I can successfully invoke and add the column. I feel I am missing something. Any ideas?

    Reply

    • Hi Steve,
      you will have to invoke the function manually or at least enter the table name manually by using a dummy table first and then tweaking the code.
      /Imke

      Reply

  13. Thank you, Imke. I am now able to understand and use your function. As you mentioned, it is much, much faster. I have a table with 13,000 records, refresh time went from about five minutes to a few seconds.

    Reply

    • Thanks Steve for the feedback, improvement sounds great 🙂
      My I ask which technique you had used before ?
      /Imke

      Reply

  14. Sure. I was using an expression found it Ken Puls / Miguel Escobar’s book on page 185.

    #”Added Custom” = Table.AddColumn(Transactions, “Previous Day Cases”, each try Transactions [Cases] { [Index] – 1 } otherwise [Cases]),

    I had a column of data that showed total COV19 cases by day, by county, in the state of Texas. There are 254 counties and dates since March 4. I need a previous day column in the the current row to do the math and determine new cases. Amounted to 254 time # days (57 through April 30) rows. Total rows now at 14478.

    Reply

  15. Hi Imke,

    This is really very fast indeed as compared to Index method. I am trying to use this function but I need to generate list of values only if particular condition is meet. i.e. I have ID and Value column and have created ID.Next column ( referring next row of ID column). Now I want to get Next row from Value column if [ID]=[ID.Next] else it should give Current Value only. I am unable to generate list with such condition. 🙁

    Reply

    • Hi Mukesh,
      I would do this in 2 steps:
      1) Generate a column with the previous row value with my method.
      2) Add column that checks the condition and generates the final value.
      /Imke

      Reply

  16. Thank you so much for this function! Just like many others mentioned my refresh time went from minutes to seconds.
    As I am just getting into Power Query I have no clue if the following is possible, but I’d neet to practically pass two arguments for the 4th parameter. I have a column with Articles and one with Dates and would need it to group it by “Arcticle manufactured on set Date”.
    Is that possible with your function?

    Thanks a lot in advance!

    Reply

  17. This is great! Thank you so much for making this public for all to access – what a blessing!
    When I invoke the function, it’s creating a whole new table. Is there any way to call this function to just create additional columns that you need in your existing table rather than creating a whole new table? Thank you so much!
    Kellen

    Reply

    • Hi Kellen,
      the reason why this function is so fast is that it doesn’t operate on a row-by-row basis.
      You can apply it in your existing query by adding a new step. No need to create a new query and new table for it.
      /Imke

      Reply

      • Imke,

        Thanks so much for your response! I am still having issues trying to get the function to be able to add columns onto the existing table (“Combined_Jira”) without creating an additional table (“Invoked Function”). Maybe my set up is incorrect:

        I am creating your function with the name ReferenceDifferentRow with the “bells and whistles” query.
        I am now trying a couple different things without success to accomplish the task listed above:
        a. I choose to Invoke Custom Function in the Add Column tab, but I am not able to choose the current table (“Combined_Jira) that I am in that I want my other row’s values to be added to.

        b. I go to the ReferenceDifferentRow function and I enter in the parameters with the Combined_Jira table but it gives me a new table, “Invoked Function.”

        c. I go into my Applied Steps of Combined_Jira and add a new step with the following code = ReferenceDifferentRow(Combined_Jira, null, {“Last_Status_Date”}, {“Jira_Num”}, null, null)
        It only returns, “Expression.Error: A cyclic reference was encountered during evaluation.” which makes sense because it’d be caught in an infinite loop.

        d. I try to invoke the function by using your call above (= Table.ReferenceDifferentRow(Combined_Jira, null, {“Last_Status_Date”}, {“Jira_Num”}, null, null)
        It only returns, “Expression.Error: The name ‘Table.ReferenceDifferentRow’ wasn’t recognized. Make sure it’s spelled correctly.”

        I hope that that makes sense. I just don’t want my table to be duplicated with the additional rows, if possible, I’d like to have the additional rows on the current/preexisting table. This is by far the fastest way I’ve seen.

        Thanks again!

        Kellen

        Reply

  18. I created a table in Excel with two columns, Date and Value. 20 rows, dates from 1/1/2020 through 1/20/2020, Values 1 to 20. Brought the table into Power Query. Excel table name is “Data”. First step in PQ is named “Source”. Insert a new step suing the fx button to the left of the formula bar. That step will reference the Source step. Replace source with the function fnGetPreviousRow(Source, “Value”). New column with Previous Row values appears.

    fnGetPrevious Row is Imke’s function.

    https://drive.google.com/file/d/1tRpDzIEvlzza9G7fMERl_heJl6jEGKjd/view?usp=sharing

    One thing I have learned is to not invoke the function from the UI.

    let
    Source = Excel.CurrentWorkbook(){[Name=”Data”]}[Content],
    Custom1 = fnGetPreviousRow(Source, “Value”)
    in
    Custom1

    Date Value Previous Row
    1/1/2020 12:00:00 AM 1 null
    1/2/2020 12:00:00 AM 2 1
    1/3/2020 12:00:00 AM 3 2
    1/4/2020 12:00:00 AM 4 3
    1/5/2020 12:00:00 AM 5 4
    1/6/2020 12:00:00 AM 6 5
    1/7/2020 12:00:00 AM 7 6
    1/8/2020 12:00:00 AM 8 7
    1/9/2020 12:00:00 AM 9 8
    1/10/2020 12:00:00 AM 10 9
    1/11/2020 12:00:00 AM 11 10
    1/12/2020 12:00:00 AM 12 11
    1/13/2020 12:00:00 AM 13 12
    1/14/2020 12:00:00 AM 14 13
    1/15/2020 12:00:00 AM 15 14
    1/16/2020 12:00:00 AM 16 15
    1/17/2020 12:00:00 AM 17 16
    1/18/2020 12:00:00 AM 18 17
    1/19/2020 12:00:00 AM 19 18
    1/20/2020 12:00:00 AM 20 19

    Reply

  19. If anyone finds this really cool solution to the problem, I had a solution that worked better for me after finding out that the “rotation” in the script is a real memory hog.

    It seems to be caused by Table.ToColumns and Table.FromColumns. On my machine which is 16GB, with my 500k row table, this hits 12GB and starts to thrash… and I’m not sure it ever finishes.

    What I ended up doing is sort of like the age-old Index column remedy, with a twist.

    I added an index to the original table.

    I created a copy of the table with the same index column, but for this table, i offset the index by N which is the number of rows of offset I want.

    In DAX, I create an Index table using DISTINCT(oneTable[index]), and relate it to each talble.

    Then I do the queries in DAX using RELATED(otherTable), which lets me access the tables with index columns instead of the method using Index alone.

    I haven’t done performance testing, but I think as I’m using a real related column, this should be faster than using the filter for [Index] = curIndex-1

    Cheers!

    Reply

  20. The easiest way is to duplicate the table & assign index to both cloned and master table. Master table index starting at 0 & cloned table index starting at 1. Map both table by the index.
    The other way is to integrate python script which much easier and clean

    Reply

    • Hi John,
      yes, these are the methods I was referring to in my intro.
      They will be slow or very slow when applied on large datasets.

      Reply

  21. Hello, im trying to make this simple calculation (I show what I have done in Excel) and im trying to do this in PowerBI Query Editor, but I don’t know how could I do this formula in Power BI. Is there any way to make an operation betweern one row and previous row?

    0 A B

    1 5 =($A1-$A0)
    2 7 =($A2-$A1)
    3 9 =($A3-$A2)
    4 12 =($A4-$A3)
    5 13 =($A5-$A4)

    I hope you understand what I’m trying to do.

    Thank you!

    Reply

  22. Hi Imke,

    I thought this was a really slick way of getting the previous row, however, I needed to return multiple columns from the previous row, so I modified it to accept a list of column names and to also set the Types of the new columns based on the Types from the original columns.

    Here’s the code. What do you think?

    llet func =
    (MyTable as table, MyColumnNames as list) =>
    let
    Source = MyTable,
    PrevColumnNames = List.Transform(MyColumnNames, each “Prev.” & ),
    ColTypes = List.Transform(MyColumnNames, each Type.TableColumn(Value.Type(Source),
    )),
    ShiftedLists = List.Transform(MyColumnNames, each {null} & List.RemoveLastN(Table.Column(Source, _),1)),
    ColumnLists = Table.ToColumns(Source) & ShiftedLists,
    NewTable = Table.FromColumns(ColumnLists, Table.ColumnNames(Source) & PrevColumnNames),
    Result = Table.TransformColumnTypes(NewTable, List.Zip({PrevColumnNames,ColTypes}))
    in
    Result
    in
    func

    Reply

  23. It looks like WordPress uses underscores for Italics. There should be one after the ampersand in PrevColumnNames and one after the comma (Value.Type(Source),) in ColTypes.

    Reply

  24. Hi Imke,

    Very much enjoy your posts.

    I have tried your suggestion to a query that has been very slow churning through 600,000 records (about 10 mins) using the index matching method

    So I have implemented your approach and the query is actually 6 times slower.

    Here is the original merge

    Source = AllTransMerge,
    Merge = Table.NestedJoin(Source,{"PrvAll_ID"},Source,{"All_ID"},"Prv"),
    #"Expand Next" = Table.ExpandTableColumn(Merge, "Prv", {"ItemID", "StoreID", "TransDate", "QtyChg", "CompQty"}, {"Prv.ItemID", "Prv.StoreID", "Prv.TransDate", "Prv.QtyChg", "Prv.CompQty"}),

    and the new merge

    Source = AllTransMerge,
    SourceCopy = Table.SelectColumns(Source, {"ItemID", "StoreID", "TransDate", "QtyChg", "CompQty"}),
    ColNamesPrev = List.Buffer( Table.ColumnNames( SourceCopy ) ),
    SourcePrev = #table( ColNamesPrev, List.Repeat( { List.Repeat( {null}, List.Count( ColNamesPrev ) ) }, 1 )) & Table.RemoveLastN( SourceCopy , 1 ),
    MergeCols = Table.ToColumns( Source ) & Table.ToColumns( SourcePrev ),
    TableFromCols = Table.FromColumns( MergeCols , Table.ColumnNames( Source ) & List.Transform( ColNamesPrev, each "Prv."&_ ) ),

    Have I implemented it incorrectly? It produces the same results, so I don’t think so – but I am not sure how it could be that much slower with so many records

    Thoughts?

    Charlie

    Reply

    • Hi Charlie,
      to get the performance improvement, you must use my function as it is and not just copy specific code from it.
      If you don’t know how to use the whole function on your solution, please let me know and I will record a video for it.
      Cheers, Imke

      Reply

      • Hi Imke,

        I implemented it with your function – see below – and the speed was still 6 times slower than using merged indexes – very odd

        Source = AllTransMerge,
        MergedTable = fnTable_ReferenceDifferentRow( Source, null, {"ItemID", "StoreID", "TransDate", "QtyChg", "CompQty"}, null, ".Prv" ),

        Thoughts?

        Reply

        • Hi Charlie,
          yes, the function with all the “bells and whistles” might be slower sometimes.
          If you’re really after performance, please stick to the first “Table.PreviousRow” function and clear up your data afterwards.
          Interested to hear how this performs.
          Thanks, Imke

          Reply

          • Hi,

            I thought my first submission was just a multi column version of your Table.PreviousRow – no added bells and whistles. And performance was the same as using your function.

            Still the difference in performance (6 times slower) between your proposed approach and the merging based on indices is stunning to me – any ideas why?

            Is it because there are so many rows of data? processing of 2M rows which are reduced to 600K rows of the source that is being processed in this merging query? Have you run your approach will large data sets?

            Thanks

          • Hi Charlie,
            sure, the reason for my blogpost was an application with millions of rows back then.
            I just re-ran the tests and could see that the index-method became much faster than 3 years ago, when I published this article.
            However, at least with my sample data, the index method is still slower. For 1 Mio rows it is taking 70 seconds to refresh with the index method and half as much (36 seconds) with my method on my machine.
            Please find the file here: https://thebiccountant-my.sharepoint.com/:u:/g/personal/imke_thebiccountant_onmicrosoft_com/ERFJhFqjVWZHtkWFbCinSFkByW2UyS0Di6jXttKw2cVHjA?e=kGen2S
            The time advantage of my method was much larger compared to the index and other methods when I wrote the blog. And given the nature of my method where I chunk op the data into columns and re-assemble, I could well imagine that for applications where complex transactions happen before the previous row operation, the index method could actually be faster.
            Thanks for bringing this to my attention!
            Cheers,
            Imke

  25. It is my understand that one should not use the “Invoke Custom Column” but should implement as below. This started with an Excel file with one column, named “Column A”. Works perfectly. Table name is Source.

    let
    Source = Excel.CurrentWorkbook(){[Name=”Data”]}[Content],
    Custom1 = fnPreviousRow(Source, “Column A”)
    in
    Custom1

    Reply

    • Just to be clear, fnPrevousRow is a query in the same workbook that contains Imke’s code. The function is called and returns a table with the previous row column.

      Reply

  26. Hi Imke,

    I came across your method and was really psyched to implement this function for my small source table (approx 17,000 rows, 40 columns). However when I added the function as a new step, my preview starts reading a really big endless amounts of data and doesn’t stop (original source file is approx. 7 Mb but the preview goes into multiple hundred Mb and never stops until the system crashes. Of course it runs forever. Has anybody reported that ever?

    Cheers,

    Henrik

    Reply

  27. Pingback: Refer to Previous Row and Next Row in Power Query - Goodly

  28. Pingback: Get Value from Previous Row using Power Query - BI Gorilla

  29. Hi, Imke!
    Here’s the way wich is faster and it works with very big tables:
    let
    tbl = Table.FromColumns({{“a”..”i”},{1..9}},{“a”,”b”}),
    AddOtherRowColumn = (tbl,col,newcol,index) =>
    [ lst = List.Buffer(Table.Column(tbl,col)),
    add = Table.AddIndexColumn(tbl,newcol,index,1),
    f = if index if x lst{x}?,
    to = Table.TransformColumns(add,{newcol,f})][to],
    to = AddOtherRowColumn(tbl,”b”,”c”,-1),
    to1 = AddOtherRowColumn(tbl,”b”,”c”,-3),
    to2 = AddOtherRowColumn(tbl,”b”,”c”,2)
    in
    to2

    Reply

Leave a Reply