Power Query Recursion: Simple reference of same row

[TuqueLogic]

Just looking to do a simple recursive reference but Power Query doesn't seem to be simple with recursion.

Table with 3 columns: 
Index,  Date,  AdjustedDate

Date is a datetime var, imported data
Index is an ascending index assign at import
AdjustedDate is for the recursion function

This is the logic to employ:
AdjustedDate = 
If Table[Date] at index > Table[AdjustedDate] at index-1
then Table[Date] at index 
else Table[AdjustedDate] at index-1

Can't just add an offset row for Date as the Date row used can be an undetermined number of rows up.

Thanks

[v-saisrao-msft]

Hi @TuqueLogic,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If any of the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[TuqueLogic]

I 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]

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
    ToTable

If 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] 
)

[MarkLaf]

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
    AddPreviousHigher

Output:

Edit: realized I got the recursive logic wrong, tweaked to reflect OP's clarificaiton

[slorin]

Hi @TuqueLogic 

another solution with Table.Group and GroupKind.Local

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtU3MAQipVgdJK4RCtfQAFXWAlXWGIMbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}),

Group = Table.Group(ChangedType, {"Date"}, {{"Data", each _}}, GroupKind.Local, (x,y)=> Byte.From(x[Date]<y[Date])),
Rename = Table.RenameColumns(Group,{{"Date", "AdjustedDate"}}),
Expand = Table.ExpandTableColumn(Rename, "Data", {"Date"}, {"Date"})
in
Expand

Stéphane 

[TuqueLogic]

The dates aren't relevant to the adjusted date. IE 2 entries in the date column can be the same date but have 2 different adjusted dates.

[dufoq3]

Hi @TuqueLogic, check this:

version with index (slower)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIyMDLVNzAEIqVYnWglQ2QhI7CQEZKQoQFYyBhZlQVYyARZlTFYyBRNKBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Date", type date}}),
    Ad_AdjustedDate = Table.AddColumn(ChangedType, "Adjusted Date", each let a = try ChangedType{[Index]-1} otherwise [Date=[Date]] in if [Date] > a[Date] then [Date] else a[Date], type date)
in
    Ad_AdjustedDate

much faster version (index column is not needed)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtU3MAQipVgdJK4RCtfQAFXWAlXWGIMbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Ad_PevDate = Table.FromColumns(Table.ToColumns(ChangedType) & {{null} & List.RemoveLastN(ChangedType[Date], 1)}, Value.Type(Table.FirstN(ChangedType, 0) & #table(type table[PrevDate=date], {}))),
    Ad_AdjustedDate = Table.AddColumn(Ad_PevDate, "Adjusted Date", each if [Date] > ([PrevDate] ?? [Date]) then [Date] else ([PrevDate] ?? [Date]), type date),
    RemovedColumns = Table.RemoveColumns(Ad_AdjustedDate,{"PrevDate"})
in
    RemovedColumns

[TuqueLogic]

Thanks for the reply.

I don't see how this is recursive.  But that may just be me.

The date to use is not necessarily only 1 row previous relative to the index. 
The date to use may be many rows previous.

As well the column index is needed as the original dates are not necessarily relevant to the operation.

If it was only a shift of 1 or 2 I was concerned with I would just created index shifted merged columns and evaluate at a row level with DAX.


And here all I did was replace the hashtable that determines the initial input and it doesn't work in this cae.
I don't understand why not, but this is the result.

[PwerQueryKees]

Ah! But in excel, every expression is evaluated on the level of a cell. In PowerQuery the expression is evaluated for the whole column (or table or whatever you pass it) at once.

In other words, Excel Formulas and M expressions work completely different.

And "at index" is not part of the syntax. You can refer to a row as {Index} but there is no concept of previous row in M.

My advice: Try to learn PowerQuery M language if you need heavy data crunching in excel, but never assume it to be similar to excel formula language in any way, even if there seem to be similarities.

Kees Stolker

A big fan of Power Query and Excel

[TuqueLogic]

I know they work differently.
I could have used python, C, etc as the example (It's just a for loop with an IF) but gave Excel as it's a common reference point in this forum.

Note, you can do index/row references in power query in a couple different ways

But I can figure out how to reference the same column as is being calculated.
It's the self column ref in combination that is the issue for me.
IE a dynamic self referenced column.

[TuqueLogic]

This is the logic of the function:
AdjustedDate = 
If Table[Date] at index > Table[AdjustedDate] at index-1
then Table[Date] at index 
else Table[AdjustedDate] at index-1

Or in another fashion:
x = Table[Date] at index
y = Table[AdjustedDate] at index-1

 if x>y then x else y

If it were 2 columns A, B in excel it would be (starting at B2)
B2 = IF( B2>A1, B2, A1)

[PwerQueryKees]

PQ definitely support recursive functions. Your example does not contain any function though, so I do not understand your question.

FYI a recursive function: (the @sign before the call of the function #"!" tells PQ it is a recursive call)

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel