Power Query Problems

Keep the Three Most Recent Dates in Excel Power Query

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 869 views

Question details

Filter a table to retain all records from the three most recent dates present in the data.

Keep the Three Most Recent Dates in Excel Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
The dataset contains records for several dates, and the user wants to keep records from only the latest three distinct dates.
Observed behavior
The desired result includes every row for the three latest dates in the dataset.
Before you start

Check the name of your Excel table and its date column, and make sure the date values can be converted to the Date type. The example below uses a table named Table1 and a column named Date.

Solution 1Recommended

Filter to the Three Latest Distinct Dates with Power Query

Use this method to keep all rows for the three most recent dates in the dataset, including multiple records on the same date.

1
Open the table in Power Query

Select a cell in your Excel table, then choose Data > From Table/Range. If prompted, confirm that your data has headers.

2
Open Advanced Editor

In Power Query Editor, select Home > Advanced Editor. Replace the existing code with the following, changing Table1 and Date to match your table and column names: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Typed = Table.TransformColumnTypes(Source, {{"Date", type date}}), Dates = List.RemoveNulls(Table.Column(Typed, "Date")), LatestDates = List.FirstN(List.Sort(List.Distinct(Dates), Order.Descending), 3), Result = Table.SelectRows(Typed, each List.Contains(LatestDates, [Date])) in Result

3
Apply the query

Select Done, verify the preview contains the expected records, and choose Home > Close & Load to return the result to Excel.

Filter to the Three Latest Distinct Dates with Power Query
Dates versus rows: Keeping the top three rows after sorting by date is not equivalent: it can exclude other records on those dates. This query keeps all rows for the latest three distinct nonblank dates.
Free Microsoft Office alternative

Work with Date-Based Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative for opening and editing spreadsheets in a familiar interface. Its Excel format support can help you move existing files over, though it does not fix or replace an Excel Power Query query.

Review and filter spreadsheet records by dateOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface for easier migrationWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does this return the last three calendar days?

No. It returns the three most recent distinct dates present in the table, even if there are gaps between them. A rolling calendar-day filter requires a different condition.

What happens if several records have the same date?

The query retains every row whose Date value matches one of the three latest distinct dates. Sorting and keeping only three rows would not guarantee that.

What if the table contains fewer than three dates?

The query keeps rows for all available nonblank dates. Rows with blank dates are excluded.

How do I update the result after adding data?

Add the records to the source Excel table, then use Data > Refresh All. Power Query will recalculate the three latest dates.

Why does the date conversion show errors?

Check that the Date column contains valid dates and that its name matches the code in Home > Advanced Editor. Text dates may need a matching locale when you change their type.