Forum Discussion
Merge based on date ranges
I am fairly new to PowerBI and really can seem to find a specific or even related solution online to my problem.
The issue:
Table1 is a list of Sales persons with Start date / End date and target values
| Person | Start | End | Target |
| SP1 | 10/01/2023 | 01/12/2023 | 10000 |
| SP2 | 15/04/2023 | 19/05/2023 | 100 |
| SP3 | 01/01/2023 | 31/08/2023 | 315 |
Table2 is a list of dates that starts from the List.Min(Start) to List.Max(End) of Table1.
I need to merge Table2 to Table1 but for every date (row level) in Table2 for each sales person within each start to end range.
the output would end up showing a row for every date in Table2 duplicated for each sales person between their respective Start and End date.
I have already played around with merge and creating additional join columns but I can't seem to get the correct output. I know this can also be done with Dax using crossjoin and filter but I have to build this into power query.
Thanks in advance
I need to merge Table2 to Table1You don't need to do that. Merges are bad for your health.
Let the data model do the work for you. Worst case use GENERATESERIES and INTERSECT in DAX to solve this.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
2 Replies
I need to merge Table2 to Table1You don't need to do that. Merges are bad for your health.
Let the data model do the work for you. Worst case use GENERATESERIES and INTERSECT in DAX to solve this.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thank you for the info - sadly I went the merge route.
