Forum Discussion
Join Tables based on date range - one registration matched with multiple dates
Hello to all!!!
after search and questions to GPT, I came to the last resort....
I have table A with column
Weekending |
14/1/2023 |
21/1/2023 |
28/1/2023 |
4/2/2023 |
11/2/2023 |
18/2/2023 |
25/2/2023 |
4/3/2023 |
11/3/2023 |
I have table B with columns
start | end | action |
14/1/2023 | 4/2/2023 | A |
21/1/2023 | 4/2/2023 | B |
I want to join them if value of Weekending is between start and end, so I would have the following result
Weekending | start | end | action |
14/1/2023 | 14/1/2023 | 4/2/2023 | A |
21/1/2023 | 14/1/2023 | 4/2/2023 | A |
28/1/2023 | 14/1/2023 | 4/2/2023 | A |
4/2/2023 | 21/1/2023 | 4/2/2023 | B |
11/2/2023 | 21/1/2023 | 4/2/2023 | B |
18/2/2023 | 21/1/2023 | 4/2/2023 | B |
25/2/2023 | 21/1/2023 | 4/2/2023 | B |
thanx!
Hello jorge_terere
Check the attached PBIX, I have created a similar scenario by inducing a CROSS JOIN and then filtering out rows based on your condition(Between start and end) using Power Query.
I do not know the size of your original tables, Power query would be able to manage the cross join without any issues if the table expands to few million rows.
Let me know if this helps and if you have any question.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
4 Replies
NaveenGandhiMemorable Member
Hello jorge_terere
Check the attached PBIX, I have created a similar scenario by inducing a CROSS JOIN and then filtering out rows based on your condition(Between start and end) using Power Query.
I do not know the size of your original tables, Power query would be able to manage the cross join without any issues if the table expands to few million rows.
Let me know if this helps and if you have any question.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!Hi NaveenGandhi !
Indeed, solved my issue and I thank you.
You help me see another perspective in order to solve and other familiar problems.
Still, you must understant the problem of multiplying large data sets. The logic you use is tableA rows X tableB row. For example 1mil *100k is a huuuuuge number.
Still, is a solution!!!
Thank you!
- NaveenGandhi
Absolutely, 1 mil*100k is a huge number, but once filtered with the required condition the numbers should be less and if it's still higher we would need to incorporate some more steps like leveraging parameters to reduce the dataset in powerbi desktop and expanding it back in service. Which is a good practice to improve performance.
Thanks,
Naveen
even though logic work, it created massive issues.
the time for Load become to much and size of save file from 2mb went to !!!
I have to multiple 40k row with 520 rows by using this method...
Unfortunatelly not applicable.
But thanks for sharing it!
