Forum Discussion
Joining two tables where TABLE1.Datetime is between TABLE2.StartDatetime and TABLE2.EndDatetime
See if this helps. It returns this in Power Query:
To get this result, add a column to your Table 1 with this formula:
Table.SelectRows( Table2, (Ranges) => ([Datetime] >= Ranges[StartDateTime]) and ([Datetime] <= Ranges[EndDateTime]) )That will return a nested table from Table2 that has only the records in that range.
Then just expand that added custom column.
Community ChampionSee if this helps. It returns this in Power Query:
To get this result, add a column to your Table 1 with this formula:
Table.SelectRows(
Table2,
(Ranges) => ([Datetime] >= Ranges[StartDateTime])
and
([Datetime] <= Ranges[EndDateTime])
)That will return a nested table from Table2 that has only the records in that range.
Then just expand that added custom column.
Thanks for your help, the solution you provided works perfectly.
I found an article yesterday that was matching between dates and was able to modify the steps to do the same with a Datetime but decreased the time resolution portion of datetime to reduce the subsequent generated rows using...
= Table.AddColumn(#"Removed Columns", "TimestampHH:SS", each [Timestamp] - #duration(0,0,0,Time.Second([Timestamp])))
Below are the applied steps I used to create the intveral match I was looking for...
- = Table.AddColumn(#"Renamed Columns", "TotalMinutes", each Duration.TotalMinutes([ShiftPlannedEnd] - [ShiftPlannedStart]), Int64.Type)
- = Table.AddColumn(#"Inserted Date Subtraction", "Timestamp_hh:mm", each List.DateTimes([ShiftPlannedStart],[TotalMinutes]+1,#duration(0,0,1,0)))
- = Table.ExpandListColumn(#"Added Custom2", "Timestamp_hh:mm")
- Then used "Merge Queries as new" using a left outer merge to create the final table.
But your solution seems much more efficient J
- edhans
Glad it helped out Tgilchrist . I was trying to do it without an actual merge.
As you've discovered though, if there is one way to do it in Power Query, there are probably 10 ways to do it! 😁
