Merge Tables and Keep the Latest Activity Date in Excel Power Query
Question details
Combine overlapping records from multiple systems and find the latest activity date for each person and activity.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Tables from different systems contain overlapping people and activities that need to be standardized and consolidated.
- Observed behavior
- The desired result is one row per person and activity, showing the most recent date and allowing the data to be refreshed.
Identify the person, activity, and date columns in each source, and decide how equivalent names and activity values should be standardized. Check that the dates can be converted to a consistent date type.
Append and group the tables in Excel Power Query
Use this method to consolidate the sources and produce a refreshable latest-date summary.
In Excel, select Data > Get Data and choose the appropriate source for each table. Open each query in the Power Query Editor.
In each query, rename the corresponding columns to the same names, such as Person, Activity, and Date. Use Transform > Format and Replace Values as needed to make equivalent person and activity values match.
Select each Date column and use Transform > Data Type > Date. Check for conversion errors or blank dates and correct them as appropriate.
Select Home > Append Queries > Append Queries as New. Choose Three or more tables, add the three standardized queries, and select OK.
In the appended query, select Home > Group By and choose Advanced. Add Person and Activity as grouping columns, then create a latest-date column using the Max operation on Date.
Select Home > Close & Load to place the grouped result in Excel. When source data changes, select Data > Refresh All to run the queries again.

Work with consolidated activity tables in WPS Spreadsheets
WPS Office is a free, lightweight alternative for opening and editing spreadsheet data in a familiar interface. It supports common Excel formats for an easier move between office suites, although it does not fix Excel Power Query issues.

Frequently Asked Questions
Why are some records for the same person grouped separately?
Differences in spelling, capitalization, or extra spaces can create separate groups. In each source query, use Transform > Format or Replace Values to standardize the Person and Activity values before appending.
What happens if a date cannot be converted?
The Date type conversion may produce an error. Select the Date column in Power Query, inspect the error rows, and correct the source values or use an appropriate locale when converting the data type.
Will Refresh All automatically include a new source file?
Refresh All updates the sources already included in the queries. To include new files automatically, set up a folder-based source with a consistent file structure; otherwise, add the new file as a query and include it in the append.
Do I need to remove duplicate rows before grouping?
No. Exact duplicate rows do not change the maximum date. If you remove duplicates, avoid deduplicating on Person and Activity alone before grouping, because that may remove the row with the latest date.




