Joining 3 Data Sources with a Time Condition - Followup

[bgierwi2]

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

[bgierwi2]

@v-menakakota 

That gets the result I was looking for!

[bgierwi2]

@amitchandak 

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!

[v-menakakota · Accepted solution]

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

[amitchandak]

@bgierwi2 , Please see if the attached file can help 

[bgierwi2]

@amitchandak 

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

[amitchandak]

@bgierwi2 , Now join is 1-2 and 1-3 . File attached after signature