February 16, 2018
This is a best practice kind of question, rather than a practical "how do I get x, y and z to happen".
I have a data source for our employees including their pay rates. Their pay rates change over time, not just once a year necessarily.
Each month I take the data from their time sheets (hours) which allocates time to our activities and pull in the rates from the data source. This is then available as a report on the cost of the various activities undertaken.
So, how do I ensure that the data for, say, January 2018 is correct if the data source rates change over time? Obviously I could just not refresh the query or the spreadsheet but what if I forget? What if someone else uses it and refreshes it?
Now, it happens that the data source at present is a spreadsheet table that I maintain more or less manually, so I could potentially keep multiple versions and change the source when I create the next month's report when the data changes to point to the newer version. And that is probably what I'll do, but I am interested to see if there is any kind of best practice approach (I did a google search but nothing seemed to come up).
Also, it is feasible that in the future the data source will become something I can't control, that is, it will come straight from our payroll system.
July 16, 2010
I'd have two tables; Time Sheets and Pay Rates.
The Pay Rates table will contain the employee details like employee ID, name, rate, rate effective date. You can then do the equivalent of a VLOOKUP approximate match in Power Query by merging the tables to bring in the rate from the Pay Rates table to the time sheet table based on the date fields. Power Query VLOOKUP approximate match is explained here.