Forum Discussion
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
fact table
Solution SQL
I have no pbix for this yet and just would like some input how this could be possible?
Best,
Jame5Blonde
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.
4 Replies
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.
Thanks for a swift reply, I will try out these steps and return to you guys. Much obliged.
This worked liked clockwork big "+" with the optimized version.
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?
