Power Query Problems

How to Merge Tables Using Overlap Conditions in Excel Power Query

Elise Williams Oct 7, 2026 870 views

Question details

The user wants to join two tables based on matching route identifiers and overlapping numerical mile ranges using greater-than and less-than conditions.

How to Merge Tables Using Overlap Conditions in Excel Power Query

Product: Excel Power Query
Device & OS: not provided
Scenario: Merging datasets where exact matching is insufficient, requiring range overlap conditions (non-equality joins) for accurate combination.
Observed behavior: Power Query's standard merge dialog only permits exact equality joins, lacking a direct way to merge rows using greater-than or less-than overlap conditions.

Before you start

Ensure both tables are loaded into Power Query as connections and contain a shared exact-match column (like a route identifier) alongside your range columns (such as from_mile and to_mile).

Solution 1Recommended

Merge via Exact Match and Filter by Custom Overlap Logic

Since Power Query does not natively support non-equality joins in its interface, you must merge on an exact match first, expand the data, and then apply custom logic to filter overlapping ranges.

This workaround mimics a SQL non-equality join by creating a Cartesian-like product for matching IDs and subsequently filtering out rows that do not meet the overlap criteria.

Merge on Route Identifier

From the Power Query Home tab, select 'Merge Queries'. Choose your primary table (Table A) and your secondary table (Table B). Select the shared route identifier column in both tables to perform a standard Left Outer or Inner Join, then click OK.

Expand the Merged Table

Click the expand icon at the top of the newly created merged column. Select the range columns (e.g., from_mile and to_mile) from Table B to add them to your current table, and uncheck 'Use original column name as prefix'.

Add an Overlap Condition Column

Go to the Add Column tab and select 'Custom Column'. Create a formula to check for overlap: `if [TableA.from_mile] < [TableB.to_mile] and [TableA.to_mile] > [TableB.from_mile] then true else false`. Name this column 'IsOverlap'.

Filter the Results

Click the filter dropdown on your new 'IsOverlap' column and select only 'true'. This filters the expanded dataset to show only the rows where the numerical ranges successfully overlap.

Calculate the Overlapping Range

If you need the exact overlapping distance, add another Custom Column using `List.Max({[TableA.from_mile], [TableB.from_mile]})` as the new start point, and `List.Min({[TableA.to_mile], [TableB.to_mile]})` as the new end point.

Merge via Exact Match and Filter by Custom Overlap Logic

Performance Tip: Because expanding merged queries can significantly increase row counts before filtering, consider buffering your tables using Table.Buffer() or filtering out irrelevant rows before performing the initial merge.

Free Microsoft Office alternative

Experience Seamless Data Management with WPS Office

If complex data transformations and limitations in standard Microsoft Office tools are slowing you down, consider switching to WPS Office. It provides a lightweight, intuitive spreadsheet environment equipped with robust functions to manage large datasets and execute advanced conditional logic effortlessly.

1. Download and Install: Download WPS Office for free from the official website and follow the quick installation process.
2. Open Your Excel File: Launch WPS Spreadsheet and seamlessly open your existing .xlsx workbooks without losing any formatting or data.
3. Apply Advanced Logic: Utilize built-in advanced lookup and array formulas as an alternative to complex queries for managing overlapping range conditions.

Free to use with a familiar, easy-to-navigate interface identical to classic spreadsheet software.Fully compatible with Microsoft Excel file formats, including .xlsx, .xls, and .csv.Lightweight architecture ensures fast loading and seamless processing of extensive overlapping datasets.Features a powerful built-in formula library for complex range lookups and dynamic array filtering.

microsoft office alternative - wps office

Frequently Asked Questions

Does Power Query support direct SQL-like non-equality joins?

No, the standard Power Query Merge dialog only permits exact equality joins. To join by conditions like greater-than or less-than, you must perform an exact match merge first, expand the table, and apply custom conditional filtering.

How do I define an overlapping range mathematically in Power Query?

Two ranges overlap if the starting point of Range A is strictly less than the ending point of Range B, and the ending point of Range A is strictly greater than the starting point of Range B.

Can I use Table.NestedJoin for overlap conditions?

Table.NestedJoin still requires an exact key match. However, you can write custom M code using Table.AddColumn and Table.SelectRows to iterate through another table and find matching overlap rows without relying on the UI's standard merge feature.

Excel & Data Problems