Forum Discussion
Joining 2 Data Sources with a Time Condition
I am looking to join 2 data sources, that come from 2 indpendent sources.
The goal is to make sure that the railcar numbers match between the two.
But we use the same railcars, so I only want to join the 2 sources if they are within 2 weeks of each other. Ideally start with the batch number date as day 1 and match if the railcar date is within 2 weeks of that
This is sample data for the first data source:
Batch Car Data
| 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 |
This is the second data source
Railcar Data
| Railcar Number | Date |
| 1 | 10/2/2025 |
| 2 | 10/3/2025 |
| 33 | 10/4/2025 |
| 4 | 12/4/2025 |
I am hoping to combine them, and have the final result look like this:
| 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/2025 |
The top 2 rows match because the railcar numbers are the same, and the dates are within 2 weeks of each other
The 3rd entry didn't match because the railcar numbers are different (3 vs 33). So each row matched with an empty field
And the 4th entry didnt match because the date was greater than 2 weeks. So each row matched with an empty field.
Every entry should have a partner, we are just checking for errors.
I could not figure out how to match and get the time component incoprorated and for each row to match with an empty.
Or I am open to any ideas if there is a better way to check for errors
bgierwi2 , I have done a merge twice, after adding the calculated column, and I have merged again. The file is attached after the signature
6 Replies
- bgierwi2Advocate I
That worked!
I would have not though to merge it twice like you did, but my data set is showing the data the way it should.
Thank you so much! This is really helpful, exactly what I was looking for.
- amitchandakSuper User
bgierwi2 , Hope you already tried Power Query Merge - Full Join ?
Power Query Functions one must know: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=36407s- bgierwi2Advocate I
A full outer join would merge the tables the way I would like.
Is there a way to incoprorate a date condition?
I would only like to have them joined if the dates of the row are within 2 weeks of each other. If the difference in dates is more than 2 weeks, I don't want them to match.
- amitchandakSuper User
bgierwi2 , Create a new column with date diff after merge and remove the rows that is greater than 2 weeks
if( Durations.Days([Date2] -[Date]) >14, 1,0)
Remove rows with value 1 of filter and keep rows with 0