Forum Discussion
Joining 3 Data Sources with a Time Condition - Followup
On a previous post, I recieved a solution of combining two data sources with a time condition.
This was the post: Joining 2 Data Sources with a Time Condition
This was solved by amitchandak
I am now looking to see if the same thing can be done by combining 3 data sources with an outer join.
Where the other 2 data sources have a condition that match if they are within 2 days.
The referenced solution with 2 datasources worked perfectly, now just looking to add another data source.
The 2nd source (Railcar) and 3rd Source (Switch) both reference the Railcar Number of the 1st source (Batch). And the dates must be within 14 days of the 1st source.
| Batch Number | Railcar Number | Date |
| A | 1 | 10/1/2025 |
| B | 2 | 10/2/2025 |
| C | 3 | 10/3/2025 |
| D | 4 | 10/4/2025 |
| Railcar Number | Date |
| 1 | 10/2/2025 |
| 2 | 10/3/2025 |
| 33 | 10/4/2025 |
| 4 | 12/4/2025 |
| Switch Number | Date |
| 1 | 10/3/2025 |
| 2 | 10/4/2026 |
| 3 | 10/5/2025 |
| 44 | 10/6/2025 |
I'd like to do an outer merge with a calculation to get below. Where every entry that didn't match with the 1st source in the date range has its own row (Outer Join)
The same thing as your solution above, just adding another datasource.
| Batch Number - Batch | Railcar Number - Batch | Date - Batch | Railcar Number - Rail | Date - Rail | Railcar Number - Switch | Date - Switch |
| A | 1 | 10/1/2025 | 1 | 10/2/2025 | 1 | 10/3/2025 |
| B | 2 | 10/2/2025 | 2 | 10/3/2025 | ||
| 2 | 10/4/2026 | |||||
| C | 3 | 10/3/2025 | 3 | 10/5/2025 | ||
| 33 | 10/4/2025 | |||||
| D | 4 | 10/4/2025 | ||||
| 4 | 12/4/2026 | |||||
| 44 | 10/6/2025 |
Hi bgierwi2 ,
Based on the latest requirements, I have tested it on my end. Could you please review the screenshots and the PBIX file below.If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Community Support Team
6 Replies
bgierwi2 , Please see if the attached file can help
That is very close to what I am what I am looking for.
It looks like it is going to match all 3 rows only if all 3 match.
Is it possible to have the rows B and C matchup with the first row independently?
Railcar 2 matches with Table B but not Table C
Railcar 3 matches with Table C but not Table B
Your example is close, but it looks like it only matches if all 3 match.
Is matching B and C independently with A possible?
Batch Number - Batch Railcar Number - Batch Date - Batch Railcar Number - Rail Date - Rail Railcar Number - Switch Date - Switch A 1 10/1/2025 1 10/2/2025 1 10/3/2025 B 2 10/2/2025 2 10/3/2025 2 10/4/2026 C 3 10/3/2025 3 10/5/2025 33 10/4/2025 D 4 10/4/2025 4 12/4/2026 44 10/6/2025 bgierwi2 , Now join is 1-2 and 1-3 . File attached after signature
That looks like what I am looking for. Let me review.
One followup on both this and the previous example:
In this situation, it is possible for my railcar numbers to repeat as we use the same fleet of cars.
So in this example we could have any of numbers 1, 2, 3, or 4 come back later in the data set with later dates.
In the examples you had sent, if you add another row with a repeat Railcar Number, you get an error due to the relationship and if you remove the relationship you get multiple matches that don't meet the time condition
I was hoping that the time condition would prevent the multiple matches.
Is it possible to allow for repeat cars in any data set, and have them only match when they match on number and the time condition (14 days)?
Using the example before:
Batch Number Railcar Number Date A 1 10/1/2025 B 2 10/2/2025 C 3 10/3/2025 D 4 10/4/2025 E 1 12/4/2025 Same as earlier, except a repeat Car 1
Railcar Number Date 1 10/2/2025 2 10/3/2025 33 10/4/2025 4 12/4/2026 2 12/6/2025 Same as earlier, except a repeat Car 2
Batch Number - Batch Railcar Number - Batch Date - Batch Railcar Number - Rail Date - Rail A 1 10/1/2025 1 10/2/2025 B 2 10/2/2025 2 10/3/2025 C 3 10/3/2025 33 10/4/2025 D 4 10/4/2025 4 12/4/2026 E 1 12/4/2025 2 12/6/2025 The bottom 2 rows with the repeat cars match with nothing because they are outside the data range
If allowing for repeat cars can be resolved on both data sets, this should resolve the entire project!
Hi bgierwi2 ,
Based on the latest requirements, I have tested it on my end. Could you please review the screenshots and the PBIX file below.If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Community Support Team
That gets the result I was looking for!
