merging data sets based on dates

Anonymous  - Since an employee presumably can't be hired until their previous employment has terminated, you could rank the Hires and Terminations. 

ImkeF has a great solution here. Basically, you will need to do the following in Power Query:

1. For each table (Hires and Terms) Go to Advanced Editor and add a step like this:

Partition = Table.Group(<Previous Step Name>, {"EmployeeId"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"StartDate", Order.Ascending}}), "Index",1,1), type table}}) 

(If it can't be guaranteed that a Term will follow each Hire, then you may also need to add Location to the Grouping.)

2. Expand the table to retrieve the columns you want (EmployeeId, StartDate, Location, and the new Index.

3. Now you can Merge the Terms with the Hires table (Left outer Join on the EmployeeId and Index columns.) This will preserve every hire row and add relevant term rows.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.