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
slorin
Super User
1 year agoHi 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
Helper I
1 year agoThe 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.