Find or Remove Duplicate Rows in a Large Excel Table with WPS Office Power Query
Question details
The user needs to find or remove duplicate rows in a large Excel table where the first two name columns define duplicates, but the complete 10-column rows must be kept.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A large table contains 10 columns; duplicate variations in the first and last name columns make exact row deduplication unreliable, and the user wants separate unique and duplicate result sets.
- Observed behavior
- Duplicate rows may be missed or incorrectly matched because name fields contain extra spaces, hidden characters, inconsistent capitalization, or punctuation differences such as "DA - VINCI" vs "DA-VINCI".
Open the workbook in WPS Spreadsheets and verify that the table has a clear header row. Keep the original data intact on a Source sheet so you can compare the unique and duplicate outputs after the query runs.
Use Power Query to clean name columns and remove duplicate rows
Best for large tables and inconsistent data because Power Query applies Trim, Clean, and consistent capitalization before duplicate detection while keeping all 10 columns.
Power Query treats values like "DA - VINCI" and "DA-VINCI" as different because punctuation and hidden characters are part of the text. Cleaning the key columns first reduces false mismatches, but you should still review remaining variations separately.
In WPS Spreadsheets, click inside the table, go to the Data tab, and choose From Table/Range in the Power Query group. Confirm the range has headers and click OK.
Select the first name column, right-click the header, and choose Transform > Trim. Repeat for the last name column, then use Transform > Clean to remove non-printable characters.
Right-click the last name column and choose Transform > Capitalize Each Word. Apply the same formatting option to the first name column so both key columns are consistent.
Select the first name column, hold Ctrl and select the last name column. On the Home tab, choose Remove Duplicates. WPS checks only the selected key columns but returns all 10 columns in the resulting rows.
Right-click the cleaned query in the Queries pane and choose Duplicate. In the duplicate query, go to Home > Keep Rows > Keep Duplicates to produce the duplicate result set.
Click Close & Load To, choose Table, and select a new worksheet for the unique results. Repeat the same loading step for the duplicate query.
Quickly remove duplicates with the Data tab command
Use this for small or already standardized tables when you only need a single unique result set and do not need Power Query cleaning.
Deduplicate large Excel tables with WPS Spreadsheets Power Query
WPS Spreadsheets includes Power Query and built-in data cleaning tools, so you can trim, clean, standardize capitalization, remove duplicate rows, and load unique or duplicate results to new worksheets without switching to another Office suite.
- 1. Open your Excel file in WPS Spreadsheets: Launch WPS Office, open the workbook, and click a cell inside the table.
- 2. Load the table into Power Query: Go to Data > From Table/Range, confirm the range includes headers, and click OK.
- 3. Clean and deduplicate: Use Transform > Trim and Clean on the name columns, select both key columns, and choose Home > Remove Duplicates.
- 4. Load unique and duplicate results: Use Close & Load To to send unique rows to a new worksheet, and duplicate the query to keep duplicate rows separately.
Frequently Asked Questions
Why does Power Query still treat "DA - VINCI" and "DA-VINCI" as different values?
Because Power Query matches exact text strings, and punctuation, spaces, and hidden characters are part of the value. Apply Trim and Clean first, then manually review remaining punctuation differences if exact matching is required.
Can I check duplicates using only the first two columns but keep all 10 columns?
Yes. In the Power Query editor, select only the first and last name columns before choosing Remove Duplicates. WPS removes duplicate keys but returns the full row for each unique record.
How do I get both unique and duplicate rows?
Create one query that removes duplicates and another duplicate of the same query that uses Home > Keep Rows > Keep Duplicates. Load each query to a separate worksheet with Close & Load To.
Does WPS Spreadsheets have a simpler way to remove duplicates without Power Query?
Yes. Select the table, go to Data > Remove Duplicates, choose the first two name columns, and click OK. This is faster but does not clean hidden characters or inconsistent capitalization first.
