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

Power BI Connection String Capitalization

Slava Murygin ran into a problem with capitalization on connection strings with Power BI Report Server: The message actually says:“Several errors occurred during data refresh. Please try again later or contact your administrator.”SessionID: 1b80301e-3898-417a-af9c-2e77ec490728[0] -1055784932: Credentials are required to connect to the SQL source. (Source at SQLServerName;DBA_Pro.). The exception was raised by the IDbCommand interface.[1] […]

Read More

Getting Prior Year’s Year-To-Date with DAX

Kasper de Jonge takes a look at how to calculate a prior year’s year-to-date over the same period as the current year: Well maybe.. what happens here is that the DAX engine took the whole date range we have in context and shifts it back 12 months. This means for year 2019 it will use […]

Read More

Categories

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