Forum Discussion
comparing datetime and choosing the nearest one
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"
Super UserHi 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.
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])When i am expanding this new column i get "No columns were found." I checked the data and there are definitely columns that match these conditions.
