Merge Tables using foreign key and date range

[EoghanSpillane]

I have two tables, Time which stores timesheet data, and Assignments which stores an employee's Role at a given time. The important columns of each are shown below.

Time

Person	Date
A	16/06/2024
A	16/07/2024
B	16/07/2024

Assignments

Person	StartDate	EndDate	Role
A	01/06/2024	30/06/2024	Role-1
A	01/07/2024		Role-2
B	01/06/2024		Role-3

Importantly, each assignment has a start and an end date. I would like to do an inner join of Assignment into Time, taking into account the date of each Time entry, and fetching the corresponding Assignment entry based on start and end dates. Desired result below:

Person	Date	Role
A	16/06/2024	Role-1
A	16/07/2024	Role-2
B	16/07/2024	Role-3

I can model this easily in SQL:

Spoiler

SELECT
Time.*, Assignments.*
FROM Time
INNER JOIN Assignments
ON Time.Person = Assignments.Person
AND Time.Date >= Assignments.StartDate
AND Time.Date <= Assignments.EndDate;

Unfortunately my tables are coming from separate sources so it is not possible to perform this in SQL, and it must be done in Power Query. I cannot quite figure out how to do this join, as the Merge Tables feature seems to only support equality, and cannot handle comparisons like this.

[EoghanSpillane · Accepted solution]

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. 

[dufoq3]

Try to think about it or even better - try other solutions, maybe you will find some benefits.

[Anonymous]

Hi @EoghanSpillane ,

@dufoq3 Thanks for your reply!

And @EoghanSpillane , inner join will not achieve your desired result because your two tables are in a many-to-many relationship, and Power Query will not be able to match between a single row. 

I read @dufoq3 's reply and his logic is to use today's date to fill in the missing EndDate and then compare the Date with Start and End. The program is correct.

And I can offer another method.
You can optionally create a custom column in the Time table using the following M Function:

let
    _Date = [Date],
    _Person = [Person],
    MatchRow = Table.SelectRows(Assignments, each [Person] = _Person and [StartDate] <= _Date and [EndDate] >= _Date),
    MaxRole=List.Max(Table.SelectRows(Assignments,each [Person]=_Person)[Role]),
    Result = try MatchRow{0}[Role] otherwise null
in
    if Result<>null then Result else MaxRole

And the final output is as below:

Since there are several rows in your Assignments table that do not have an EndDate, I'm guessing that for each different Person, the row that does not have an EndDate is the last row for their Person.
So my logic is: compare the Date column in the Time table with the StartDate and EndDate in the Assignments table respectively, and if the Date falls within that date range interval and the Person is the same, return the Role for the corresponding row in the Assignments table. If the Date does not exist in any of the Start-End intervals, the Assignment has not yet ended, i.e., there is no EndDate, then return the largest Role corresponding to the Person in the Assignments table.

Here is the whole M function in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00zcw0zcyMDJRitVBCJkjhJzQhGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Date = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each let
    _Date = [Date],
    _Person = [Person],
    MatchRow = Table.SelectRows(Assignments, each [Person] = _Person and [StartDate] <= _Date and [EndDate] >= _Date),
    MaxRole=List.Max(Table.SelectRows(Assignments,each [Person]=_Person)[Role]),
    Result = try MatchRow{0}[Role] otherwise null
in
    if Result<>null then Result else MaxRole)
in
    #"Added Custom"

Best Regards,
Dino Tao
If this post helps, then please consider Accept both of the answers as the solution to help the other members find it more quickly.

[dufoq3]

Hi @EoghanSpillane, check this:

Result

let
    TblTime = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00zcw0zcyMDJRitVBCJkjhJzQhGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Date = _t]),
    ChangedTypeTime = Table.TransformColumnTypes(TblTime,{{"Date", type date}}),
    TblAssignments = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1Dcw0zcyMDIBcowNkDhB+TmpuoZKsToIheYwOQWYvBFY3gndILi8sVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, StartDate = _t, EndDate = _t, Role = _t]),
    ChangedTypeAssignments = Table.TransformColumnTypes(TblAssignments,{{"StartDate", type date}, {"EndDate", type date}}),
    ReplacedNull = Table.ReplaceValue(ChangedTypeAssignments, null, Date.From(DateTime.FixedLocalNow()), Replacer.ReplaceValue, {"EndDate"}),
    Buffered = Table.Buffer(ReplacedNull),
    StepBack = ChangedTypeTime,
    Ad_Role = Table.AddColumn(StepBack, "Role", each Table.SelectRows(Buffered, (x)=> x[Person] = [Person] and x[StartDate] <= [Date] and x[EndDate] >= [Date])[Role]{0}?, type text )
in
    Ad_Role

[EoghanSpillane]

I'd be very keen to use this and mark as accepted solution if you can provide an explanation as to what's going on. Not a fan of incorporating a large black box into my report without understanding how it works.