Merging Queries based on time between two times

[Anonymous]

Hi there,

I need your thankful assistant for the below issue

I have calls data and duration as below

Caller ID    Call Start                          Call End

2030          9/1/2020 12:00:00 AM     9/1/2020 12:05:00 AM

2020          9/1/2020 12:01:00 AM     9/1/2020 12:05:00 AM

I have tickets data as below

Caller ID     Ticket Created

2030           9/1/2020 12:03:00 AM

2020           9/1/2020 12:03:22 AM

2030           9/1/2020 12:04:00 AM

2030           9/1/2020 12:06:00 AM

These are the only important columns

what i need exactly is to merge the two tables where each call has one or more tickets by below criterias:

• Same Caller ID
• Where Ticket Created is between Call Start And Call End

As below

Caller ID    Call Start                          Call End                            Ticket Creation

2030          9/1/2020 12:00:00 AM     9/1/2020 12:05:00 AM     9/1/2020 12:03:00 AM

2030          9/1/2020 12:00:00 AM     9/1/2020 12:05:00 AM     9/1/2020 12:04:00 AM

2020          9/1/2020 12:01:00 AM     9/1/2020 12:05:00 AM     9/1/2020 12:03:22 AM

I wish you could help as it's really important, many thanks in advance

[HotChilli · Accepted solution]

In Power Query, add a column to the first table similar to this

Table.SelectRows(TableY,
                (r) =>
                    r[Ticket Created] > [Call Start]
                    and
                    r[Ticket Created] < [Call End]
                    and
                    r[Caller ID] = [Caller ID]
            )[Ticket Created]

Swap in the name of the second table for TableY and the correct field names