Merging Tables with Date Condition

[kellzp22]

Hi, 

I am trying to merge two tables together. The first table is a jobs table, which provides an employee their start time, end time and  location. I'd like to merge this will a bed allocation table, so that if the shift is at the same location and within the allocation date range, then the Job. No. should be allocated to the Bed Allocation table.

Jobs Table: 

Bed Allocation:

Bed Allocation (Once Merged):

[v-stephen-msft · Accepted solution]

Hi @kellzp22 ,

You can use the Merge fetaure.

Sample data

Jobs:

Bed Allocation:

1.Select 'Merge Queries as New', and then set as follows, click OK.

2.Expand all columns.

3.Add a custom column.

4.Filter out the rows with a custom column value of 1, then remove unnecessary columns, and the final result.

Result:

You can check more details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Maha1]

What if some Locations has no data in Allocation table, they will be filtered, I want to keep the null values?

[VijayP]

@kellzp22 
in the both the Tables you must have similar column (1 column atleast) which is now missing,

So, Create a columns in both Tables with Location & EndDateTime and the try merging based on that column in both the tables to get the result !Share you kudos