Forum Discussion
TuqueLogic
Helper I
1 year agoPower Query Recursion: Simple reference of same row
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 da...
- 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
TuqueLogic
Helper I
1 year agoThis 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)