Reza Rad shows how to build merge joins between date ranges in Power Query:
Customer’s table has the history details of changes through the time. For example, the customer ID 2, has a track of change. John was living in Sydney for a period of time, then moved to Melbourne after that.
The problem we are trying to solve is to join these two tables based on their customer ID, and find out the City related to that for that specific period of time. We have to check the Date field from Sales Table to fit into FromDate and ToDate of the Customer table.
This is a common type 2 SCD scenario. I’d be concerned that this solution would not work with large data sets which may already be pushing the size limits of the Vertipaq engine.