How to Flag the Highest Version Number for Each ID in WPS Power Query
Question details
The user needs a Power Query method to mark the highest version number for each unique ID as TRUE and all older versions as FALSE.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The source table contains multiple rows per ID with a version number, and the user wants to identify the newest row for each ID.
- Observed behavior
- The expected output is a table with an added flag column where the highest VersionNo per ID is TRUE and all other rows are FALSE.
Open the workbook in WPS Office Spreadsheet and make sure your table contains ID and VersionNo columns with the correct data types before you open Power Query Editor.
Use a Reference Query, Sort, Remove Duplicates, and Merge
Use this method when you need a clear, refreshable query that keeps every row in the original table and flags only the highest VersionNo for each ID.
A reference query creates a helper table of only the latest ID and VersionNo pairs. Merging that helper back into the original query preserves all older rows and lets you replace unmatched values with FALSE.
In WPS Office Spreadsheet, select the data range, go to the Data tab, and choose From Table/Range to open Power Query Editor.
In the Queries pane on the left, right-click the original query and select Reference. In the new query, keep only the ID and VersionNo columns.
Click the ID column, then on the Home tab select Sort Ascending. Next click the VersionNo column, then select Sort Descending.
Go to the Home tab, choose Index Column > From 1. Select the ID column, click Remove Duplicates on the Home tab, and keep the first row for each ID, which is the highest VersionNo.
Click Add Column > Custom Column, name it IsHighest, enter the formula true, and click OK. This marks the remaining highest-version rows as TRUE.
In the original query, go to Home > Merge Queries. Select the current table and the reference query, hold Ctrl and select ID and VersionNo in both tables, set Join Kind to Left Outer, and click OK.
Click the expand icon in the merged column, select only IsHighest, and click OK. Select the IsHighest column, go to Transform > Replace Values, replace null with false, and click OK.
Flag the Highest Version Number per ID Directly in WPS Office Power Query
Use WPS Office Spreadsheet's Power Query Editor to create a reference query, keep only the highest VersionNo for each ID, merge the TRUE flag back into the original table, and replace null values with FALSE without losing any rows.
- 1. Install WPS Office: Download and install WPS Office from the official WPS website if it is not already installed on your device.
- 2. Open Power Query Editor: Open WPS Spreadsheet, select your data range, and click Data > From Table/Range to open Power Query Editor.
- 3. Create the highest-version helper: Right-click the query and select Reference, keep only ID and VersionNo, sort ID ascending and VersionNo descending, then click Home > Index Column > From 1 and Home > Remove Duplicates with ID selected.
- 4. Add and merge the TRUE flag: Click Add Column > Custom Column to add a TRUE flag, then in the original query click Home > Merge Queries and join by ID and VersionNo.
- 5. Load the flagged result: Expand the IsHighest column, select Transform > Replace Values to replace null with false, and click Close & Load to return the table to the worksheet.
Frequently Asked Questions
Why does Remove Duplicates keep the highest VersionNo and not just the first row?
Because you sort ID ascending and VersionNo descending first. Remove Duplicates keeps the first occurrence for each ID, which is the row with the maximum VersionNo.
The merged flag column is empty. How do I show FALSE for older versions?
Select the flag column in Power Query Editor, click Transform > Replace Values, enter null as the Value To Find and false as Replace With, then click OK.
Can I apply this method to a table with many extra columns?
Yes. Keep the extra columns in the original query. The reference query only needs ID and VersionNo, and the merge adds the flag column without removing the other columns.
Does WPS Office support the same Reference, Merge, and Replace Values features as Excel Power Query?
Yes. Open Data > From Table/Range in WPS Spreadsheet to access the Power Query Editor, where Reference, Sort, Remove Duplicates, Merge Queries, and Replace Values are available.
Will the TRUE/FALSE flags update when I add new version rows later?
Yes. After adding new rows to the source data, refresh the query from Data > Refresh All, and the reference query and merge will recalculate the highest versions.
