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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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!
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
- 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. - 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
- 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
- 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
- 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
- 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 😉
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
Hi Chris,
you can use the dots in the names only in the libraries (which only work in PBI and not Excel at the moment: https://www.thebiccountant.com/2017/10/06/create-a-function-library-in-power-bi-using-m-extensions/)
So you have to edit the function name sample code.
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.
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"})Pingback: Unravel cumulative totals to their initial elements in Power BI and Power Query – The BIccountant
@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
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
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 😉
Pingback: Writing data to GitHub using Power Query only – The BIccountant
A huge thanks this is a super useful function and you did it like a pro 🙂
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!
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
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!
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.
Hi Steven,
sounds as if you’re looking for incremental refresh. Natively, this is only supported in Power BI Premium.
(There are several workarounds, but they are hacky and all have their drawbacks: https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/ )
/Imke
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.
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.
Pingback: Covid-19 dashboard with Power Query / Power Pivot / Excel
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?
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
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.
Thanks Steve for the feedback, improvement sounds great 🙂
My I ask which technique you had used before ?
/Imke
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.
Thanks Steve,
yes, that’s the slowest version I’m aware of.
/Imke
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. 🙁
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
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!
Yes, sure. The 4th argument accepts a list of columns. So you could group by article and date like so: {“Article”, “Date”}
/Imke
Thank you! Works like a charm!
Glad to hear it worked for you 🙂
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
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
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
Hi Kellen,
I should do a video for this soon, will post link here.
/Imke
Excellent! Thank you, Imke!
Hi Kellen,
have uploaded a quick video here: https://youtu.be/2f6vU3Bgi1g
Does it sort out for you?
/Imke
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
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!
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
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.
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!
I solved with index column 🙂
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
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.
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
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
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?
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
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
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
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.
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
Pingback: Refer to Previous Row and Next Row in Power Query - Goodly
Pingback: Get Value from Previous Row using Power Query - BI Gorilla
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