Compare dates in two tables to return corresponding value to that date

[TurloughHendry]

I am working with HR information. I have two tables one table is time sheets which are exported weekly, and i am trying to intergrate a change in circumstances table. The reason I am trying to do this is that the weekly timesheet export will only showthe current job role, department and contracted hours of the employee.

I needs to be able to look back retrospectively to the Change In Circumstances, and be able to with a new column, say if the timesheet is on eg. 02/01/2022 Joe's job role is Junior Analyst not Senior Analyst

Ideally a table like this:

[TurloughHendry]

Currently i have tried adding a custon column in power query with this:

let mydate=[Timesheet date] in
Table.SelectRows([Change In Circumstances], each [Date Chnaged] <= mydate)

It returns the correct first change, but if the job changes more than one, for the second and thrid job change, it duplicated leaving me with this:

[wdx223_Daniel]

you're almost there. 

suppose your data in change log table is already sorted by date, then you just need to get the last record.

let mydate=[Timesheet date] in
Table.LastN(Table.SelectRows([Change In Circumstances], each [Date Chnaged] <= mydate),1)

[TurloughHendry]

Thanks for the response, its appreciated!!

For some one that goes through more than two jobs changes, it seems to only pull through the second, and not the thrid:

When ideally it needs to be: