PowerQuery - How to merge tables with conditions other than "="

Anonymous

I have two tables with linear referencing data in them in an Excel sheet that I would like to join whenever the mile ranges overlap, The table below shows the table A with attribute 1 and Table b with Attribute 2.

In SQL the query join would be this: A.route_id=B.route_id and a.from_mile<b.to_mile and a.to_mile>b.from_mile

The final table query would look like

select a.route_id, greatest(a.from_mile,b.from_mile) as from_mile, least(a.to_mile, b.to_mile) as to_mile, a.atribute1, b.attribute2

from TABLE_A a

JOIN TABLE_B b ON A.route_id=B.route_id and a.from_mile<b.to_mile and a.to_mile>b.from_mile;

Can a join like that be entered into PowerQuery for excel? I used to do this with Microsoft Query and some little SQL. Specifically wondering about the join conditions that use the "<" and /or ">" operators?

BTW this type of query could as easily be described with data that has begin and end date ranges instead of mile measures. If that makes more sense. Just change the from and to miles to some example date ranges that overlap.

Table A
route_id	from_mile	to_mile	attribute1
a	0	5	XYZ
a	5	10	ABC

Table B
route_id	from_mile	to_mile	attribute2
a	0	3	100
a	3	10	200

Join A to B	(showing all columns)
route_id	A.from_mile	A.to_mile	B.from_mile	B.to_mile	attribute1	attribute2
a	0	5	0	3	XYZ	100
a	0	5	3	10	XYZ	200
a	5	10	3	10	ABC	200

Final Table (calculating the output from and to miles)
route_id	from_mile	to_mile	attribute1	attribute2
a	0	3	XYZ	100
a	3	5	XYZ	200
a	5	10	ABC	200

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

12 answers

Anonymous

2024-03-30T12:05:48+00:00

You may need to:

and then:
Was this answer helpful?

2 people found this answer helpful.

0 comments No comments
Anonymous

2024-03-27T21:52:43+00:00

I'm going to have an explore of the Table.NestedJoin documentation to see if that isn't possible.

I've had a play around with a few things and updated the file I linked to earlier with a new (connection only) query Merge2. They are in small steps which will be able to be reduced. In the Added Custom2 step, the final column Overlap shows the extent an overlap, so if that column shows a value greater than 0 that is the overlap extent, otherwise there is none. Maybe you can do something with it.
Was this answer helpful?

0 comments No comments
Anonymous

2024-03-27T21:11:28+00:00

Thanks for reply but the measure resolution is undefined (same as if the ranges were made up of timestamps) so I don't think this will work for the general case.

The solution I put there worked but it was not in the join but in a calculated field filtered after the merged table is created. I am surprised that you cannot specify joins with more flexible conditions or just substitute an SQL statement to do the join in PowerQuery, seems like a big loss of functionality in that one used to be able to do with Microsoft Query.

Thank you for responding I can think of a few things I could use your post for though!
Was this answer helpful?

0 comments No comments
Anonymous

2024-03-27T20:56:54+00:00

If the granularity/resolution is 1 mile then perhaps the linked-to workbook below:

https://app.box.com/s/0alli8mz6c4az4fg49ubyxbyubk4cdhe
Was this answer helpful?

0 comments No comments
Anonymous

2024-03-27T20:43:10+00:00

Found this post which solved it by adding a calculated field to the result of the join on the equality fields and then filtering the calculated field. the calculated field formula is:

[A_FROM_MILE]<[B_TO_MILE] and [A_TO_MILE]>[B_FROM_MILE]

This returns null or true or false. Then filter the result set to return only true columns.

https://community.fabric.microsoft.com/t5/Desktop/Merge-two-tables-with-multiple-join-conditions-in-Power-Query/td-p/2538691
Was this answer helpful?

0 comments No comments

Share via

PowerQuery - How to merge tables with conditions other than "="

12 answers