Forum Discussion
Power Query Recursion: Simple reference of same row
- 1 year ago
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
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
- TuqueLogic1 year ago
Helper I
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.