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

Hi kkarol 

Just copy the below code and past it into the advance editor.

 

let
    Table1 = 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]),
    #"Changed Type" = Table.TransformColumnTypes(Table1,{{"ID", type text}, {"Start", type datetime}, {"End", type datetime}}),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8owwNjE1N1LSUTIyMDLRNTTQNTBXMDSxMrawMjRVitXBVGGoa2CmYGgGlLYyMMCjwsTKwBBuhpulkYUZkiVGFmBLzOFGYFNgDlIAMiEWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, End = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Table2,{{"ID", type text}, {"End", type datetime}}),
    Custom1 = Table.AddColumn(#"Changed Type", "End New", each  _[End]??List.Min(Table.SelectRows(#"Changed Type1",(x)=>x[End]>=_[Start])[End]))
in
    Custom1