Power Query: Conditional Merge / Lookup or Something Else?

[andrewfinberg]

I have a Reported Issues table as a fact table and an Employee Data table as a dimension table. We refresh the model weekly; new issues are added and previous issues get updated. We also receive a new Employee Data file monthly which overwrites the previous file. 

Both tables have an Employee ID field. I want to merge the Employee Department into the Reported Issues table. That is simple enough under normal circumstances. However, there is a catch: after an issue is closed, the Employee Department needs to remain static. We don't want a closed issue to "follow" the employee should they change departments in the future; the issue should remain with the department where the employee was working when they reported the issue.

Any ideas on how to accomplish this in Power Query? I'm thinking there must be a way, either using conditional merges, reference tables, or a combination of the two. I could be wrong. 

[olgad]

Hi Andrew, what is the source for the Employee file??

If the file gets overwritten with the new data, there is no way to say which department person belonged to at the time of closing the issue. So you have to introduce historisation for your employees which might be in a form of storing those monthly files with a stamp of a period or you might have a history with valid from valid to columns, just for those entries where employees change departments.  And then you would merge not only on the employee id, but employee id plus the effective period on the employee data side and employee id plus the period of the issue opened/closed whatever is the leading date there.