Forum Discussion

kkarol's avatar
kkarol
Frequent Visitor
1 year ago
Solved

comparing datetime and choosing the nearest one

Hi, I am looking for a solution to find a nearest datetime in one table after a specific datetime stamp in another. So I have two tables: Table1 ID Start End IX34572 2024-11-05 12:37:48 ...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    1 year ago

    Given your data, the following should work:

    let

//Note references to Table_1 and Table_2
//You can either put them in sepearate queries, or set them directly in this query.
    Source = Table_1,

    #"New End" = Table.ReplaceValue(
        Source,
        each [Start],
        each [ID],
        (x,y,z)as nullable datetime=>if x <> null then x
            else List.Min(List.Select(Table.SelectRows(Table_2, each [ID]=z)[End], (li)=>li>y)),
        {"End"})
in
    #"New End"
ronrsnfld's avatar
ronrsnfld
Super User
1 year ago

I'm not sure why that should be happening.

If it is because of the query, you might try buffering the tables to see if that makes any difference.

 

eg:

   Source = Table.Buffer(Table_1),
    Table2 = Table.Buffer(Table_2),

    #"New End" = Table.ReplaceValue(
        Source,
        each [Start],
        each [ID],
        (x,y,z)as nullable datetime=>if x <> null then x
            else List.Min(List.Select(Table.SelectRows(Table2, each [ID]=z)[End], (li)=>li>y)),
        {"End"})

 (Note that in the New End step we changed a table name from Table_2 to Table2) 

kkarol's avatar
kkarol
Frequent Visitor
1 year ago

that works. Thanks!