Power Query: Joining On Date Ranges

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.

Related Posts

Bubbling Up HTTP Status Errors In Power Query

Tony McGovern takes us through a method involving Power Query + M of giving end users useful information when a web request fails: So how does this relate to error tables? Like most well-documented APIs, the U.S. Census Bureau API has a page devoted to listing and describing all the possible response codes that can be […]

Read More

Power BI Embedding Techniques

Marc Lelijveld takes us through the different ways we can embed Power BI dashboards: In the end of 2018 Microsoft announced a great new feature which allows you to secure embed Power BI content to client applications or sites and keep security still in place. Actually, it almost works the same as the publish to […]

Read More

Categories

August 2017
MTWTFSS
« Jul Sep »
 123456
78910111213
14151617181920
21222324252627
28293031