comparing datetime and choosing the nearest one

Hi kkarol ,

Yes, it's possible to achieve this in Power BI by creating a DAX measure or using Power Query to find the nearest timestamp from `Table2` for each `Start` date in `Table1`, only for rows where `End` is empty.

Here’s a step-by-step approach using Power Query:

1. Load Tables:
- Load `Table1` and `Table2` into Power BI.

2. Add a Custom Column to Filter:
- Go to `Table1` in Power Query Editor.
- Add a custom column to filter `Table2` to only include rows where the `ID` matches and the `End` in `Table2` is after the `Start` date in `Table1`. Name this column `FilteredTable2`.

DAX

= Table.SelectRows(Table2, each [ID] = [ID] and [End] > [Start])

3. Extract Nearest DateTime:
- Now, for each row in `Table1` with an empty `End` column, find the nearest date from `FilteredTable2`.
- Add a conditional column to check if `End` is blank. If it is, use the `FilteredTable2` column to find the minimum date.

DAX

if [End] = null then List.Min(Table.SelectRows(Table2, each [ID] = [ID] and [End] > [Start])[End]) else [End]

4. Remove Extra Columns:
- Remove `FilteredTable2` and any other unnecessary columns to keep only the updated `End` column.

5. Close and Apply:
- Close Power Query Editor and apply changes to bring the transformed data into Power BI.

This approach uses Power Query’s row-level transformations to achieve the same logic you used in Excel with IF combinations, and Power Query handles the matching and filtering efficiently.

Please accept this as solution if it helps. Appreciate Kudos.

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
    已添加自定义

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

Hi kkarol ,

Thanks for all the replies!
Please check these replies to see if there is a solution you are looking for. If a user's reply can help you, please don't forget to accept its reply as solution so that other users can find the solution faster, thank you!

Best Regards,
Dino Tao