Joining two tables where TABLE1.Datetime is between TABLE2.StartDatetime and TABLE2.EndDatetime

[Tgilchrist]

I have two tables TABLE1 and TABLE2.

Currently there are no relationships between them but TABLE1 contains the Datetime of an event and TABLE2 contains a StartDatetime and EndDatetime for each shift. I would like to merge the two tables based on if the Datetime in TABLE1 is inbetween the StartDatetime and EndDatetime of TABLE2. 

In Qlik Sense it is wrote as...

Left Join (TABLE1)

IntervalMatch(DateTime)

Load StartDatetime, EndDatetime

Resident TABLE2;

Example Data...

TABLE1:

Event Code	Datetime
001	5/1/2020 2:40:00 PM
088	5/1/2020 6:20:00 PM
003	5/1/2020 11:11:00 AM
006	5/2/2020 4:10:00 PM
088	5/3/2020 8:33:00 AM

TABLE2:

StartDateTime	EndDateTime
5/1/2020 6:30:00 AM	5/1/2020 2:29:59 PM
5/1/2020 2:30:00 PM	5/1/2020 10:29:59 PM
5/1/2020 10:30:00 PM	5/2/2020 6:29:59 AM
5/2/2020 6:30:00 AM	5/2/2020 2:29:59 PM
5/2/2020 2:30:00 PM	5/2/2020 10:29:59 PM
5/2/2020 10:30:00 PM	5/3/2020 6:29:59 AM
5/3/2020 6:30:00 AM	5/3/2020 2:29:59 PM
5/3/2020 2:30:00 PM	5/3/2020 10:29:59 PM
5/3/2020 10:30:00 PM	5/4/2020 6:29:59 AM
5/4/2020 6:30:00 AM	5/4/2020 2:29:59 PM
5/4/2020 2:30:00 PM	5/4/2020 10:29:59 PM
5/4/2020 10:30:00 PM	5/5/2020 6:29:59 AM

Desired result:

Event Code	Datetime	StartDatetime	EndDatetime
001	5/1/2020 2:40:00 PM	5/1/2020 2:30:00 PM	5/1/2020 10:29:59 PM
088	5/1/2020 6:20:00 PM	5/1/2020 2:30:00 PM	5/1/2020 10:29:59 PM
003	5/1/2020 11:11:00 AM	5/1/2020 6:30:00 PM	5/1/2020 2:29:59 AM
006	5/2/2020 4:10:00 PM	5/2/2020 2:30:00 PM	5/2/2020 10:29:59 PM
088	5/3/2020 8:33:00 AM	5/3/2020 6:30:00 AM	5/3/2020 2:29:59 PM

Thanks in advance for any help

[edhans · Accepted solution]

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.

See this file for full details.

[Tgilchrist]

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... 

1. = Table.AddColumn(#"Renamed Columns", "TotalMinutes", each Duration.TotalMinutes([ShiftPlannedEnd] - [ShiftPlannedStart]), Int64.Type)
2. = Table.AddColumn(#"Inserted Date Subtraction", "Timestamp_hh:mm", each List.DateTimes([ShiftPlannedStart],[TotalMinutes]+1,#duration(0,0,1,0)))
3. = Table.ExpandListColumn(#"Added Custom2", "Timestamp_hh:mm")
4. 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! 😁

[Greg_Deckler]

@ImkeF @edhans