I want to merge two tables and I’m after some help on how to work with dates. I have two tables with the columns as listed below. What I want to do is merge Value1 & Value2 from the Employee Table into the Training Table. The Employee Table has multiple dated rows per ID. So I want to join the max date in the employee table that is less than or equal to the date in the training table. I want to end up with the columns as listed in Merged Table. What’s the best way to do this in Power Query?
Training Table:
EmployeeID
Training Date
Course Title
Employee Table:
EmployeeID
Date
Value1
Value2
Merged Table;
EmployeeID
Training Date
Course Title
Value1
Value2
Hi John,
Welcome to the forum. Can you please upload a sample Excel file with some data that covers all of the scenarios and your desired result. From there we can best understand what you're trying to do and help you further.
Mynda
Hi Mynda,
I've attached a mock up that show the two tables and what I want as the merged table. Hope that is clear?
Hi John,
Thanks for providing the sample file. It's much clearer now.
If you will always have the same number of records for each employee in the TrgTable and EmplTable then you can simply sort the data by the first column then by the second column. Then add an index number column to each table and join based on the Index numbers.
However, if there is a chance that there is a different number of records for each employee then you'll need to number them using the Number Grouped Data technique. This is what I've used in the file attached. I hope that points you in the right direction.
Mynda
Hi Mynda,
My original example was a bit misleading with only a couple of rows of data - Sorry about that. In my real data both tables will have differing numbers of rows for each employee. Some might have 100 and others might have 3 (I'm working with about 8 millions rows in a flat file).
I've added a few more rows of data to each source table that give a better representation of what I'm working with. Hope it is clearer?
In SQL I would use a subquery to select the max dated row for each employee in the Employee table the is <= the Training Date for the same employee
Hi John,
You can use Append. See attached. Note: it requires the column names to match.
Mynda
