Merging Queries based on time between two times

Question

Hi there,&nbsp;I need your thankful assistant for the below issue&nbsp;I have calls data and duration as below&nbsp;Caller ID&nbsp; &nbsp; Call Start&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Call End2030&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;9/1/2020 12:00:00 AM&nbsp; &nbsp; &nbsp;9/1/2020 12:05:00 AM2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;9/1/2020 12:01:00 AM&nbsp; &nbsp; &nbsp;9/1/2020 12:05:00 AM&nbsp;I have tickets data as below&nbsp;Caller ID&nbsp; &nbsp; &nbsp;Ticket Created2030&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/1/2020 12:03:00 AM2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/1/2020 12:03:22 AM2030&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/1/2020 12:04:00 AM2030&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/1/2020 12:06:00 AM&nbsp;These are the only important columns&nbsp;what i need exactly is to merge the two tables where each call has one or more tickets by below criterias:Same Caller IDWhere Ticket Created is between Call Start And Call EndAs below&nbsp;Caller ID&nbsp; &nbsp; Call Start&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Call End&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Ticket Creation2030 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/1/2020 12:00:00 AM&nbsp; &nbsp; &nbsp;9/1/2020 12:05:00 AM&nbsp; &nbsp; &nbsp;9/1/2020 12:03:00 AM2030&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;9/1/2020 12:00:00 AM&nbsp; &nbsp; &nbsp;9/1/2020 12:05:00 AM&nbsp; &nbsp; &nbsp;9/1/2020 12:04:00 AM2020&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;9/1/2020 12:01:00 AM&nbsp; &nbsp; &nbsp;9/1/2020 12:05:00 AM&nbsp; &nbsp; &nbsp;9/1/2020 12:03:22 AM&nbsp;I wish you could help as it's really important, many thanks in advance

hotchilli · Accepted Answer

In Power Query, add a column to the first table similar to this
Table.SelectRows(TableY,
                (r) =&gt;
                    r[Ticket Created] &gt; [Call Start]
                    and
                    r[Ticket Created] &lt; [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&nbsp;&nbsp;

Forum Discussion

Merging Queries based on time between two times

1 Reply

Related content

MERGE QUERIES

merging direct query queries

Merging Queries

Merge queries causing duplicate records

Merging Multiple Queries