topic Re: Power Query Recursion: Simple reference of same row in Power Query

Power Query Recursion: Simple reference of same row

TuqueLogic — Wed, 19 Feb 2025 20:48:04 GMT

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

Re: Power Query Recursion: Simple reference of same row

PwerQueryKees — Wed, 19 Feb 2025 21:00:10 GMT

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

Re: Power Query Recursion: Simple reference of same row

TuqueLogic — Wed, 19 Feb 2025 21:17:44 GMT

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)

Re: Power Query Recursion: Simple reference of same row

PwerQueryKees — Wed, 19 Feb 2025 21:33:05 GMT

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

Re: Power Query Recursion: Simple reference of same row

TuqueLogic — Wed, 19 Feb 2025 22:02:29 GMT

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.

Re: Power Query Recursion: Simple reference of same row

dufoq3 — Thu, 20 Feb 2025 08:05:42 GMT

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

Re: Power Query Recursion: Simple reference of same row

slorin — Thu, 20 Feb 2025 09:34:35 GMT

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

Re: Power Query Recursion: Simple reference of same row

TuqueLogic — Thu, 20 Feb 2025 18:32:15 GMT

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.

Re: Power Query Recursion: Simple reference of same row

TuqueLogic — Thu, 20 Feb 2025 17:19:39 GMT

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.

Re: Power Query Recursion: Simple reference of same row

TuqueLogic — Fri, 21 Feb 2025 00:32:38 GMT

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:

Re: Power Query Recursion: Simple reference of same row

MarkLaf — Fri, 21 Feb 2025 04:39:44 GMT

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

Re: Power Query Recursion: Simple reference of same row

MarkLaf — Fri, 21 Feb 2025 08:53:59 GMT

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

Re: Power Query Recursion: Simple reference of same row

v-saisrao-msft — Mon, 24 Feb 2025 06:28:17 GMT

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.

Re: Power Query Recursion: Simple reference of same row

v-saisrao-msft — Thu, 27 Feb 2025 12:58:32 GMT

Hi TuqueLogic,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: Power Query Recursion: Simple reference of same row

v-saisrao-msft — Sun, 02 Mar 2025 06:12:54 GMT

Hi TuqueLogic,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

Re: Power Query Recursion: Simple reference of same row

TuqueLogic — Tue, 04 Mar 2025 21:29:54 GMT

None of these are the solution.
I found a way in DAX for now but it is very computationally expensive.
I plan to revisit this recursion later.