Forum Discussion
Merge Tables using foreign key and date range
Not sure why it had to be made so complicated by the other answers.
All I had to do was add a column to Time with the following formula
Table.SelectRows(Assignments, (x) => x[Person] = [Person] and [Date] >= x[StartDate] and (x[EndDate] = null or [Date] <= x[EndDate])){0}?[Role]?All this does is finds related rows in the Assignments table for each row in Time, where the person matches, and the date range is satisfied. We then take the first match (if the data is correct there should always be exactly 1 anyway), and return the Role value from this row. Null is returned on no matches just for safety instead of an error.
This is very closely related to the SQL statement I provided (although I did actually want a left join, not an inner join). There is no need to transform the other tables when handling null values, they can just be handled in the filter.
Not sure why it had to be made so complicated by the other answers.
All I had to do was add a column to Time with the following formula
Table.SelectRows(Assignments, (x) => x[Person] = [Person] and [Date] >= x[StartDate] and (x[EndDate] = null or [Date] <= x[EndDate])){0}?[Role]?All this does is finds related rows in the Assignments table for each row in Time, where the person matches, and the date range is satisfied. We then take the first match (if the data is correct there should always be exactly 1 anyway), and return the Role value from this row. Null is returned on no matches just for safety instead of an error.
This is very closely related to the SQL statement I provided (although I did actually want a left join, not an inner join). There is no need to transform the other tables when handling null values, they can just be handled in the filter.
Try to think about it or even better - try other solutions, maybe you will find some benefits.