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:
Super UserMaybe 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
AddPreviousHigher
Output:
Edit: realized I got the recursive logic wrong, tweaked to reflect OP's clarificaiton