How to Fill Missing IDs and Remove Duplicates in Power Query in WPS Office
Question details
The user needs to clean a table in Power Query by assigning an existing ID to blank-ID duplicate rows, grouping records by Letter, and keeping only the required row from each group.
- Product
- WPS Office Spreadsheet (Power Query)
- Device & OS
- not provided
- Scenario
- A user is working with a grouped table in Power Query where some duplicate rows have blank IDs and wants to fill those blanks from matching populated rows, then remove duplicate records based on the Letter column.
- Observed behavior
- Blank ID cells remain in duplicate rows, and the dataset includes rows that should be removed after grouping by Letter.
Open the query in the Power Query Editor in WPS Office Spreadsheet and locate the Letter and ID columns. Confirm whether blank IDs are null or empty text, because the fill step depends on consistent blank values.
Fill Missing IDs and Remove Duplicates by Grouping and Sorting in Power Query
Use this method when you want Power Query to automatically fill blank IDs from duplicate rows and return only the required row for each Letter group.
Grouping by the Letter column creates a subtable for each group. Sorting each subtable allows Table.FillUp to copy the existing ID into the blank ID row, after which you can keep the required row and expand the grouped result.
In WPS Office Spreadsheet, select the table range and go to Data > From Table/Range to open Power Query Editor. Select the ID column, click the Data Type button on the Home tab, and choose Text or Whole Number so blanks are recognized consistently.
In the Power Query Editor, go to Home > Group By. Set the Group by column to Letter, name the new column GroupedData, and choose Operation > All Rows.
Add a custom column from the Add Column tab. Enter a formula such as = Table.AddColumn(#"Grouped Rows", "Filled", each Table.FillUp(Table.Sort([GroupedData],{{"ID", Order.Ascending}}),{"ID"})). This sorts each group and fills the blank ID from the populated row.
Edit the custom column formula to keep only the required row, for example with Table.FirstN or Table.SelectRows. Then click the Expand button on the Filled column and select the Letter and ID columns to return to a flat table.
Click Home > Close & Load to send the deduplicated result back to the WPS spreadsheet.
Clean Blank IDs and Duplicate Rows in WPS Office Power Query
WPS Office Spreadsheet includes Power Query, so you can group by Letter, fill missing IDs with Table.FillUp, remove duplicate rows, and load a clean table without switching to another Office suite.
- 1. Open WPS Office Spreadsheet and load your data into Power Query: Select the data range and click Data > From Table/Range in WPS Office Spreadsheet to open the Power Query Editor.
- 2. Group by Letter and sort each group: Click Home > Group By, choose Letter as the grouping field, select Operation > All Rows, and add a custom column that sorts each subtable by the ID column.
- 3. Fill missing IDs with Table.FillUp: Inside the custom column formula, wrap the sorted table with Table.FillUp on the ID column so blank ID cells inherit the populated ID value from the same group.
- 4. Keep the required row and expand: Refine the custom column to return only the desired row, then click the Expand button and select the output columns to flatten the grouped table.
- 5. Close & Load the cleaned result: Click Home > Close & Load in Power Query Editor to write the deduplicated table back to the WPS spreadsheet.
Frequently Asked Questions
Why does the blank ID row need to be sorted before using Table.FillUp?
Sorting ensures the populated ID is directly below the blank ID in the same group. Table.FillUp then copies that value upward into the blank row; without sorting, the populated row may not be below, so the fill cannot reliably reach the blank.
What should I do if blank IDs are stored as empty text instead of null?
Select the ID column in Power Query Editor, click Transform > Replace Values, and replace the empty text with null before grouping. This makes the blank type consistent so Table.FillUp fills all blank-ID rows correctly.
Can I keep the last duplicate row instead of the first?
Yes. Use Table.LastN or sort the subtable in the opposite direction inside the custom column before keeping the required row, then expand the grouped result.
Will this method work for many distinct Letter groups?
Yes. The Group By operation creates a subtable for every distinct Letter value. The same sort, fill, keep-row, and expand logic is applied to all groups when the query is loaded.
