Excel Power Query: Merge Duplicate Rows and Combine ZIP Codes
Question details
The user needs to consolidate rows that match across their identifying columns and combine the corresponding delivery ZIP codes into one comma-separated field.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A source table contains duplicate records with different DELIVERY ZIPCODE values, and the user wants one consolidated record for each matching group.
- Observed behavior
- Duplicate records remain as separate rows instead of being grouped with their ZIP codes combined.
Convert the source range to a table and identify every column that defines a duplicate record. Confirm that DELIVERY ZIPCODE contains the values to combine, then plan to convert that column to Text before grouping.
Group duplicate records and combine ZIP codes in Power Query
Use this method when duplicate rows match across all other columns and their DELIVERY ZIPCODE values should appear together in one comma-separated field.
Group the table by the columns that identify the duplicate record, such as Latitude, Longitude, and any other matching fields. Keep the grouped rows available so the ZIP code values can be combined, while the grouping columns preserve the remaining record information.
In Excel, select a cell in the source table and choose Data > From Table/Range. If prompted, confirm that the table has headers to open Power Query Editor.
In Power Query Editor, select the DELIVERY ZIPCODE column, open Transform > Data Type, and choose Text. Confirm the replacement if Power Query asks whether to change the current type.
Choose Home > Group By, select Advanced, and add every column that must match for a duplicate record, such as Latitude and Longitude. Set the new column name to GroupedRows and choose All Rows as the operation.
Choose Add Column > Custom Column. Name the column Combined ZIP Codes and enter =Text.Combine(List.Transform([GroupedRows][DELIVERY ZIPCODE], each Text.From(_)), ", "). This joins the grouped ZIP codes with a comma and space.
Review the grouped output, remove the GroupedRows column if it is no longer needed, and rename Combined ZIP Codes to DELIVERY ZIPCODE if appropriate. Select Home > Close & Load to return the consolidated table to Excel.
Edit and manage the consolidated spreadsheet with WPS Office
WPS Office is a free, lightweight alternative for opening, editing, and sharing the consolidated workbook after Power Query processing. Its familiar spreadsheet interface supports smooth migration and preserves common Excel file formats, while Power Query refreshes themselves require a compatible Microsoft Excel environment.
Frequently Asked Questions
Which columns should be selected in Power Query when grouping duplicate rows?
Select every column that must have the same value for records to be considered duplicates, such as Latitude, Longitude, address fields, or other identifying columns. Leave DELIVERY ZIPCODE out of the grouping keys if different ZIP codes need to be combined.
Why should DELIVERY ZIPCODE be converted to Text before combining it?
ZIP codes can contain leading zeros and are not intended for mathematical calculations. Setting the column to Text preserves those zeros and allows Text.Combine to join the values correctly.
What formula combines the ZIP codes from a grouped Power Query table?
If the grouped nested-table column is named GroupedRows, use =Text.Combine(List.Transform([GroupedRows][DELIVERY ZIPCODE], each Text.From(_)), ", ") in a Custom Column.
How can I update the merged rows after the source table changes?
Return to Excel and choose Data > Refresh All. Power Query reruns the grouping and updates the consolidated table from the current source data.
Can WPS Office refresh a Microsoft Power Query query?
WPS Office can open and edit the resulting Excel workbook, but Microsoft Power Query refreshes require a compatible Microsoft Excel environment. Use WPS for reviewing or updating the exported spreadsheet data.
