Keeping track of change

[Excelski]

Hi,

I have built a database with power query which I'm learning more about each day
I have a record of hours spent at different locations and a couple of master list for locations and rates of pay.

Everything is working fine......untill somebody gets an increment in salary then all the historical data changes too, what would be the best way to keep the historical data and update the new data with current rates?

Any and all suggestions greatly appreciated

Thanks
Kevin

[JeteMc]

I feel that the set up of the "master list[s] for locations and rates of pay" is going to be important, so it will help if you could upload a small, desensitized, sample file.
Let us know if you have any questions.

[Excelski]

I agree.....But I am not sure the best approach.
Currently, I have 2 master files, both are a series of columns with headers

Rates of Pay = Name. Job Title. Hourly rate. OT1 Rate. OT2 Rate. Travel. Ancillary Rate.

Locations = Project No. Customer. Address. Status.

Thanks for looking

Kevin

[Saqib Qureshi]

I guess addidng a "Effective Date Column" and adjusting the Formula on that basis may solve this issue.

[Excelski]

I am not sure how that would work