Power Query Problems

Find the Closest Date Within 45 Days in Excel or Power Query

Huma Ashraf ChHuma Ashraf Ch Nov 12, 2026 871 views

Question details

Create a result table that matches every date in one source table with the closest date and related values from a second table when the date difference is no more than 45 days.

Find the Closest Date Within 45 Days in Excel or Power Query
Product
Microsoft Excel and Power Query
Device & OS
not provided
Scenario
Two tables contain dates and related fields, and each record in the first table needs a closest-date match from the second table within a 45-day limit.
Observed behavior
The desired result is a refreshed table containing the closest eligible date and its corresponding values for each source record.
Before you start

Confirm that both source ranges are formatted as Excel tables and that their date columns contain real date values rather than text. Identify the key columns and decide how ties should be handled when two dates have the same minimum difference.

Solution 1Recommended

Use Power Query to return the closest match within 45 days

Use this approach for repeatable matching across larger tables or whenever the source data will be updated and refreshed regularly.

Power Query can compare the dates from both tables, calculate an absolute day difference, remove matches beyond 45 days, and keep the row with the smallest remaining difference for each source record.

1
Load both tables into Power Query

Click inside the first Excel table, open Data > From Table/Range, and choose Close & Load To > Only Create Connection. Repeat the process for the second table.

2
Create a comparison query

In Excel, open Data > Get Data > Combine Queries > Merge Queries as New. Select the two table queries and match any shared key columns, or create a comparison structure that allows each first-table row to be evaluated against the second table.

3
Calculate the date difference

In Power Query Editor, choose Add Column > Custom Column and calculate the absolute difference between the source date and candidate date, such as Number.Abs(Duration.Days([SourceDate] - [CandidateDate])).

4
Keep only eligible and closest records

Use the difference column filter to keep values less than or equal to 45. Group by the source ID and source date, sort each group by the difference in ascending order, and keep the first row before expanding the corresponding values.

5
Load and refresh the result

Select Home > Close & Load To and load the result to a worksheet. After changing either blue source table, right-click the result table and select Refresh.

Refresh after source changes: The result table updates only after the Power Query connection is refreshed. If no candidate date is within 45 days, that source row will have no eligible match unless you add a separate unmatched-row handling step.
Free Microsoft Office alternative

Try WPS Office for Excel-Compatible Date Matching Workbooks

WPS Office is a free, lightweight Microsoft Office alternative for opening, editing, and sharing Excel-compatible workbooks. Move your existing .xlsx files into a familiar spreadsheet interface, review date-matching formulas, and collaborate without changing your established workflow.

  1. 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the desktop or Start menu.
  2. 2. Open the Excel workbook: Choose Menu > Open and select the existing .xlsx workbook containing the source and result tables.
  3. 3. Review the matching worksheet: Use the worksheet tabs, formula bar, and table data to inspect the date columns and update compatible spreadsheet formulas.
  4. 4. Save an Excel-compatible copy: Choose Menu > Save As, select Excel 2007-365 Workbook (*.xlsx), and save the updated file for continued sharing.
Open and edit Excel-compatible .xlsx workbooks for date matching and lookup tasks.Use a lightweight spreadsheet interface with familiar worksheet, table, and formula tools.Migrate existing Microsoft Excel files with support for common formatting and formulas.Keep source tables and result tables organized in one workbook for easier review and sharing.
QA img-9

Frequently Asked Questions

How do I make Power Query return only dates within 45 days?

After calculating the absolute date difference in Power Query Editor with Add Column > Custom Column, open the difference column filter and keep values less than or equal to 45 before grouping and selecting the smallest difference.

What happens when no date is within the 45-day limit?

The source record will not have an eligible match after filtering. You can preserve it by using a left outer merge and replacing the missing expanded values with a label such as No match.

Can the closest date formula work in older versions of Excel?

The provided formula uses REDUCE, WRAPROWS, CHOOSEROWS, and other dynamic-array functions available in Microsoft 365 and Excel for the web. Older Excel versions may require helper columns, array formulas, or Power Query.

How do I update the result after adding rows to the source tables?

If the sources are formatted as Excel tables, add the new records directly below the existing table and right-click the Power Query result table, then select Refresh. For formulas, the table references usually expand automatically.

How are ties handled when two dates are equally close?

XLOOKUP returns the first occurrence of the minimum difference in the lookup range. Sort the candidate table first or add a secondary sorting rule in Power Query when a specific tie-breaking result is required.