Join/Merge based on date between range AND ID (with condition ISNULL, fact.date)

[Jame5_Blonde2]

Hi, 

I´ve stumbled upon a perculiar case where I need to JOIN two tables in Power Query between two dates and ID. 

In cases the support table endDate ISNULL i want to refer to the date in Fact = statDate. 

The reason i´m not doing this in SQL is becuse of server issues (fact azure sql server isn´t hosted by us) and I cant join between these two servers.  

The expanded column after merge is regionID and the expected results in Fact table is 

row 1 = regionID 6

row 2 = regionID 7

support table

fact table 

Solution SQL 

I have no pbix for this yet and just would like some input how this could be possible?

Best, 

Jame5Blonde

[smpa01]

@Jame5_Blonde2  if you have premium capacity - you can bring your data to a datamart that has a SQL end point and you can write fully qualified SQL query on that SQL DB.

If you have Fabric - you have access to SQL, Scala, Apache spark, Python, R  - the options are endless really.

If you don't have any of the above, this is doable using DAX.

For a pure PQ solution @AlexisOlson 

Is there any way, you can please provide a mock-up sample data?

[ChielFaber · Accepted solution]

I think this is what your looking for (https://exceed.hr/blog/merging-with-date-range-using-power-query/ ). 

In this blog post it's explained how to solve a similar problem. I think you can follow this instructions and get to the solution your looking for.

[Jame5_Blonde2]

This worked liked clockwork big "+" with the optimized version. 

[Jame5_Blonde2]

Thanks for a swift reply, I will try out these steps and return to you guys. Much obliged.