Forum Discussion
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 ...
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"
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"
Thanks a lot for that, looks like it works! one strange thing though, i have soruce files like 1,5 mb and when it is refreshing i see this query is loading like 1X GBs into the query, when i am saving the pbix file is 1,5 mb
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)
that works. Thanks!