Forum Discussion

TuqueLogic's avatar
TuqueLogic
Icon for Helper I rankHelper I
1 year ago
Solved

Power 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...
Need Help
  • MarkLaf's avatar
    MarkLaf
    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:

    IndexDate
    05/1/2025
    15/12/2025
    24/1/2025
    34/1/2025
    44/1/2025
    55/23/2025
    65/19/2025
    76/1/2025
    85/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

     

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
1 year ago

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's avatar
    TuqueLogic
    Icon for Helper I rankHelper I
    1 year ago

    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.