Merge Tables using foreign key and date range

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

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.