Normalize Power Query Values Against a List Parameter in WPS Office
Question details
The user wants to standardize inconsistent Power Query values by matching source data against an approved list and replacing unmatched or variant values with a controlled label such as N/A.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Cleaning and normalizing source data in Power Query by using a two-column mapping table that defines approved replacements.
- Observed behavior
- Source values may contain spelling variants or unapproved entries, and the user needs matched values unified and unmatched values replaced with N/A.
Prepare a two-column mapping table with From and To columns in WPS Spreadsheets, or make it accessible as a table or connection. Ensure the source data is also available as a table so Power Query can use both the source column and the approved list.
Map Source Values to Approved Values Using a Two-Column From/To Table
Use this when you need controlled replacements: create an explicit mapping table, merge it with the source query, and convert unmatched rows to N/A.
This method is the most reliable way to standardize inconsistent values because every allowed replacement is defined in a visible From/To mapping table. It avoids unpredictable fuzzy matches and makes future updates easy.
In WPS Spreadsheets, enter From and To as headers, list each source variant under From and the approved value under To, then format the range as a table by going to Insert > Table.
Select the source range, go to Data > From Table/Range, and click OK to open the Power Query Editor.
In the Power Query Editor, click Home > Merge Queries, select the source column and the mapping table's From column, and keep the default Left Outer join.
Click the expand icon in the new column header, choose the To column, and click OK.
Select the expanded To column, go to Transform > Replace Values, enter null in Value To Find and N/A in Replace With, then click OK.
Use Fuzzy Merge for Close Variants on Windows
Use this when source values vary by spelling, spacing, or small typos and you want Windows fuzzy matching to propose likely matches before replacing nulls with N/A.
Normalize Power Query Values Against a List Parameter in WPS Spreadsheets
Use WPS Spreadsheets to create a two-column mapping table, merge it with your source data in Power Query, and replace unmatched values with N/A. It is a free, compatible way to keep data clean without changing your existing .xlsx workbook.
- 1. Prepare the mapping table: In WPS Spreadsheets, create a two-column table with From and To headers and list the approved replacements.
- 2. Load source data into Power Query: Select the source range, go to Data > From Table/Range, and open the Power Query Editor.
- 3. Merge with the mapping table: Use Home > Merge Queries to join the source column to the From column with a Left Outer join.
- 4. Expand and replace nulls: Expand the To column, select it, and choose Transform > Replace Values to set null to N/A.
- 5. Close and load: Click Close & Load To to load the normalized table into the worksheet.
Frequently Asked Questions
How do I create the mapping table for Power Query normalization?
In WPS Spreadsheets, enter From and To as headers, list each source variant in From and the approved value in To, then select the range and choose Insert > Table so Power Query can use it.
Why do unmatched values show null after merging?
A left outer merge only joins rows where the source value matches the From column. Non-matching source rows have no related To value and return null. Use Transform > Replace Values on the expanded To column to change null to N/A.
Can I use fuzzy matching to normalize values in WPS Office?
On Windows, you can enable Use fuzzy matching in the Merge Queries dialog and set a similarity threshold. However, an explicit From/To mapping table is more controlled for approved-value replacement.
Will this method work with existing .xlsx workbooks?
Yes, WPS Spreadsheets opens .xlsx files and supports the standard Power Query merge workflow, so you can apply the same normalization steps without converting the file.
