Reply
Jame5_Blonde2
Helper I
Helper I
Partially syndicated - Outbound

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

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

support table.PNG

fact table 

fact.PNG

 

Solution SQL 

SQL.PNG

 

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

 

Best, 

Jame5Blonde

1 ACCEPTED SOLUTION
ChielFaber
Solution Supplier
Solution Supplier

Syndicated - Outbound

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.

View solution in original post

4 REPLIES 4
smpa01
Super User
Super User

Syndicated - Outbound

@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?

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
ChielFaber
Solution Supplier
Solution Supplier

Syndicated - Outbound

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.

Syndicated - Outbound

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

Syndicated - Outbound

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

avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)