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

Question

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:

anonymous · Answer

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 · Answer

you're almost there.&nbsp;
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] inTable.LastN(Table.SelectRows([Change In Circumstances], each [Date Chnaged] &lt;= mydate),1)