Forum Discussion
Helper IPower Query Recursion: Simple reference of same row
Maybe this is what you are looking for. Just a note, though, that recursions usually perform very badly in Power Query and there is usually a way more performant, non-recursive method to get the same results. This is partly why lots of people are giving you non-recursive solutions.
SourceData:
Index Date 0 5/1/2025 1 5/12/2025 2 4/1/2025 3 4/1/2025 4 4/1/2025 5 5/23/2025 6 5/19/2025 7 6/1/2025 8 5/28/2025 let Source = SourceData, AddPreviousHigher = let srcBuf = Table.Buffer( Source ) in Table.AddColumn( srcBuf, "PreviousHigherDate", each let recF = ( x as number ) => let thisDate = srcBuf{x}[Date] in if x = 0 then thisDate else let prevDate = srcBuf{x-1}[Date] in if thisDate > prevDate then thisDate else @recF( x - 1) //recursion in recF([Index]), type date ) in AddPreviousHigherOutput:
Edit: realized I got the recursive logic wrong, tweaked to reflect OP's clarificaiton
Helper II realize that using the Excel example confused the situation as I require being able to go back until a condtion is met, hence recursion.
This is the approximate form of the function DateRec()
= (x as number) as date =>
let
f=if ( DateTable{x}[Date] >= DateTable{x-1}[ImportDate] ) then DateTable{x}[Date] else DateRec(x-1)
in
if( x = 0) then DateTable{x}[Date] else f
But it is not letting me reference the column it is being used to create, DateTable[ImportDate].
Yet in the reference tutorial below, that is exactly what was done.
But the author chose the same column name as the function name so there is some abiguity in the functional relationships in the code and process.
(why the function and column were not called fibbFct and fibbCol or similar is beyond me)
https://radacad.com/fibonacci-sequence-understanding-the-power-query-recursive-function
Example of what the table should look like in the end below.
The first two columns are given, the ImportDate Column is the one to be created via the Invoke Custom Function:
- MarkLaf
Super User
Maybe this is what you are looking for. Just a note, though, that recursions usually perform very badly in Power Query and there is usually a way more performant, non-recursive method to get the same results. This is partly why lots of people are giving you non-recursive solutions.
SourceData:
Index Date 0 5/1/2025 1 5/12/2025 2 4/1/2025 3 4/1/2025 4 4/1/2025 5 5/23/2025 6 5/19/2025 7 6/1/2025 8 5/28/2025 let Source = SourceData, AddPreviousHigher = let srcBuf = Table.Buffer( Source ) in Table.AddColumn( srcBuf, "PreviousHigherDate", each let recF = ( x as number ) => let thisDate = srcBuf{x}[Date] in if x = 0 then thisDate else let prevDate = srcBuf{x-1}[Date] in if thisDate > prevDate then thisDate else @recF( x - 1) //recursion in recF([Index]), type date ) in AddPreviousHigherOutput:
Edit: realized I got the recursive logic wrong, tweaked to reflect OP's clarificaiton
- MarkLaf
In addition to my answer with a recursive function, if you are looking for an answer that I I believe still meets your criteria and performs better, take a look at this method that uses List.Generate. We take advantage of the fact that, for any given index, if the [Current Date] <= [Previous Date] then the current [Adjusted Date] should inherit from previous Index's [Adjusted Date]. Perhaps this is the 'recursion' / 'reference to previous calculation in column' behavior you are looking for?
Based on some quick tests on my machine using a query to generate x rows of Index|Date (date range set to 1/1/2025..12/31/2026), whereas my other solution with the recursive function can process 6k rows in ~35s, this can process 1m rows in the same amount of time.
let Source = SourceData1m, Ind = List.Buffer( Source[Index] ), Dat = List.Buffer( Source[Date] ), Gen = List.Generate( ()=>[i=0,adj=List.First( Dat )], each [i] < List.Count( Ind ), each [ i = [i] + 1, adj = let prevAdjAnswer = [adj], prevDate = Dat{[i]}, curDate = Dat{[i]+1} in if prevDate = null then curDate else if curDate > prevDate then curDate else prevAdjAnswer ], each [adj] ), ToTable = Table.FromColumns( { Ind, Dat, Gen }, type table [ Index = Int64.Type, Date = date, Adjusted Date = date ] ) in ToTableIf curious, here is the M I used to generate the dummy data.
SourceData1m
Table.FromRecords( List.Generate( ()=>0, each _ < 1000000, each _+1, each [ Index = _, Date = Date.From( Int64.From( Number.RandomBetween( 45657.5, 46387.499 ) ) ) ] ), type table [Index=Int64.Type, Date=date] )