Power Query Problems

Merge Tables and Keep the Latest Activity Date in Excel Power Query

Elise WilliamsElise Williams Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

Append and group the tables in Excel Power Query

Use this method to consolidate the sources and produce a refreshable latest-date summary.

1
Import each source

In Excel, select Data > Get Data and choose the appropriate source for each table. Open each query in the Power Query Editor.

2
Standardize the columns

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.

3
Set and check the date type

Select each Date column and use Transform > Data Type > Date. Check for conversion errors or blank dates and correct them as appropriate.

4
Append the tables

Select Home > Append Queries > Append Queries as New. Choose Three or more tables, add the three standardized queries, and select OK.

5
Group by person and activity

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.

6
Load and refresh the result

Select Home > Close & Load to place the grouped result in Excel. When source data changes, select Data > Refresh All to run the queries again.

Append and group the tables in Excel Power Query
Removing duplicates: Removing exact duplicate rows is optional before grouping. Do not remove duplicates based only on Person and Activity before calculating the maximum date, because that could discard the latest record.
Free Microsoft Office alternative

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.

Review and edit consolidated person and activity data in WPS SpreadsheetsOpen and save common Microsoft Excel formats, including XLSXUse a familiar spreadsheet interface for a smoother transition from ExcelWork with a free, lightweight office suite
microsoft office alternative - wps office

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.