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"
ZhangKun's avatar
ZhangKun
Icon for Super User rankSuper User
1 year ago

The data you provided and your example seem to be wrong. IX3472 does not exist

 

let
    源 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc3bCQMxDETRVoy+16CXLWcKWEgHAeP+24hCwrKY/F6ORnPS82XeQukgZfUqUrkVUVjAx1YNHlCjdfzuNO6ifwQHzLOWrzofOvqFuOoo4rAO5qz/TA5ZYYbk0NiqIz+I01pv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Start = _t, End = _t]),
    更改的类型 = Table.TransformColumnTypes(源,{{"ID", type text}, {"Start", type datetime}, {"End", type datetime}}),
    已添加自定义 = Table.AddColumn(更改的类型, "自定义", 
        each [End] ?? List.Min(Table.SelectRows(Table2, (r) => [ID] = r[ID] and r[End] > [Start])[End])
    )
in
    已添加自定义