Apply Corrections Between Power Query Tables in WPS Office Spreadsheet
Question details
The user needs to apply cell-level corrections from a second Power Query table to a raw table in WPS Office Spreadsheet without creating dozens of conditional columns.
- Product
- WPS Office Spreadsheet – Power Query
- Device & OS
- not provided
- Scenario
- Two tables share an ID column; a correction table contains values only where a cell must be corrected, and the user wants to merge the corrections back into the raw table efficiently.
- Observed behavior
- The raw and correction tables share an ID column, but only corrected cells contain values in the second table. Creating a separate conditional column for every corrected field becomes difficult to maintain.
Open WPS Office Spreadsheet and confirm that Power Query is available from the Data tab. Make sure both the raw table and the correction table use the same serial ID column and that matching attribute column names are spelled exactly the same.
Unpivot, Merge, and Pivot Back to Apply Corrections in Power Query
Use this solution when the correction table has many possible columns to fix; it replaces multiple conditional columns with a scalable unpivot-merge-pivot pattern.
Instead of writing one conditional column for each corrected field, this method treats every value as an ID–attribute pair. It compares the original and correction values at row level and restores the table with a single pivot step.
This approach works in WPS Office Spreadsheet Power Query and scales to any number of columns because the column list is handled as data, not as separate query steps.
In WPS Office Spreadsheet, click the Data tab, choose Get Data or From Table/Range, and load both the raw table and the correction table as separate queries.
In Power Query Editor, select the ID column, use Transform > Unpivot Other Columns, or right-click and choose Unpivot Other Columns, so each row becomes ID, Attribute, and Value for both queries.
On the raw unpivoted query, use Home > Merge Queries, select the correction unpivoted query, and join on both ID and Attribute columns with the default left outer join.
Expand the merged correction table column and select only the correction Value field; rename it to CorrectionValue for clarity.
Use Add Column > Custom Column, name it CorrectedValue, and enter a formula that returns the correction when the correction value is not null and the original value otherwise.
Remove the original Value and CorrectionValue columns, select the Attribute column, and use Transform > Pivot Column with CorrectedValue as the values column to restore the original table structure.
Apply Table Corrections Efficiently with WPS Office Power Query
Use WPS Office Power Query to unpivot, merge, and pivot correction tables back into a clean final dataset. This direct workflow avoids repetitive manual conditional columns and keeps the original table structure ready for analysis.
- 1. Open your workbook in WPS Office: Launch WPS Office Spreadsheet and open the workbook that contains the raw and correction tables.
- 2. Open Power Query Editor: Click the Data tab and select From Table/Range or Get Data to load both tables into the Power Query Editor.
- 3. Apply the unpivot-merge-pivot workflow: Unpivot both tables, merge on ID and Attribute, create a custom corrected value column, remove temporary columns, and pivot back to the original structure.
- 4. Load the corrected table: Click Close & Load to write the final corrected table to a new worksheet in WPS Office Spreadsheet.
Frequently Asked Questions
Why do I need to unpivot both tables before merging?
Unpivoting turns each column name into an Attribute value, so you can merge rows by both the serial ID and the attribute. This means one join handles all columns instead of requiring a separate conditional column for each field.
Which join type should I use when merging the unpivoted tables?
Use a left outer join on the raw table so all original rows remain. Rows that have no correction entry will show a null correction value and keep the original value.
How does the custom column decide between the original and corrected value?
Use a formula like if [CorrectionValue] = null then [Value] else [CorrectionValue]. It checks whether the correction table contained a value for that ID and attribute and chooses the correction only when one exists.
Can this method handle more than 43 correction columns?
Yes. Because columns are processed as attribute rows, the number of columns does not change the number of query steps or conditional columns. The pivot step rebuilds the table with the current column list.
What should I check before loading the tables into Power Query?
Confirm that both tables use the same serial ID column name and exactly matching column headers. If a column name differs, merge matching by Attribute will fail for that field.
