PowerQuery Merge Effective Date

[barjoman]

I have a set of performance data by Region, and I would like to merge this data with the appropriate Manager Name. In Power Query, a simple Left Outer Join does the trick in the normal situation where a Manager is assigned one or more regions for the entire Data Set.

My problem lies in how to address Manager changes.

Regions A and B are allocated to Manager X from Jan 1st to Jun 13th
Region C is allocated to Manager Y from Jan 1st to Jun 13th
Regions A and C are allocated to Manager X from Jun 14th onwards
Region B is allocated to Manager Y from Jun 14th onwards

One solution is to have every single RegionDate combination with the appropriate manager, but that would be extremely difficult to maintain (hundreds of thousands of combinations) and would probably suffer from some Human Error.

Ideally, we would have every region, the manager name, and the effective date in a table (much easier to maintain and only hundreds of rows. We would then only have to update the table IF there are a any changes, which are generally few and far between.

Ideas?

 

[SimonNU]

It sounds like you need to implement a Slowly Changing Dimension (SCD).

Here is one solution: #PowerQuery – Joining to a Slowly Changing Dimension

Alternatively, if you're loading this into Power Pivot then that opens up other solutions.

 

[barjoman]

This is fantastic! It looks like it will work. I'll test it and let you know if I face any issues.

The data is eventually going to be loaded into PowerPivot, but I'll be filtering for each manager in Power Query before the load occurs.