How to Keep Manual Data Aligned with Power Query Results in WPS Spreadsheets
Question details
The user needs to keep manually entered columns aligned with Power Query results when appended files refresh and source row order changes.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user appends multiple source files in Power Query, then adds manual columns next to the combined output. As new files are added, the refresh changes the row order of the appended results.
- Observed behavior
- Manual columns remain in their original worksheet positions while Power Query rows reorder, causing the manual data to become misaligned.
Before merging, identify a stable key column that exists in both the Power Query results and the manual data, such as a file name, record ID, or composite key. Make sure the key values are unique enough to avoid duplicate matches.
Merge Manual Data from a Separate Table Using a Stable Key in Power Query
Use this when you want to keep manual data separate from the source files so it stays aligned by key regardless of row order changes.
This method stores manual data in a separate table and joins it to the Power Query results by a stable key. Row order changes no longer matter because matching is based on key values, not worksheet position.
In WPS Spreadsheets, enter your manual data in a new sheet and format it as a table via Insert > Table. Include the same key column such as File Name or Record ID.
Select any cell in your source append table, go to Data > From Table/Range to open Power Query, then append or combine your source files as usual.
In the Power Query Editor, go to Home > Merge Queries > Merge Queries as New. Select the current appended query and the manual data table.
Select the key column in both tables, choose Left Outer join so all Power Query rows remain, and click OK.
Click the expand icon in the new merged column, select the manual columns you need, then click Close & Load to return the aligned result to WPS Spreadsheets.
Add Manual Columns to the Original Source Files Before Appending
Use this when you can edit the source files and want the manual data to travel with each row through the append process.
Create the Column as a Calculated Column Directly in Power Query
Use this when the manual values can be derived by a rule or formula, so you do not need to type them row by row.
Keep Manual Data Aligned with Power Query in WPS Spreadsheets
Open Power Query from the Data tab, append source files, and use Merge Queries as New to join a separate manual data table by a stable key. This keeps manual columns aligned after every refresh.
- 1. Open Power Query in WPS Spreadsheets: Launch WPS Office Spreadsheets, click the Data tab, and choose From Table/Range or Get Data to load your appended source data into Power Query.
- 2. Prepare the manual table: In a separate sheet, create a table with the same key column plus the manual columns, then select Insert > Table to format it.
- 3. Merge queries by key: In Power Query Editor, go to Home > Merge Queries > Merge Queries as New, select the source query and the manual table, highlight the shared key columns, and choose Left Outer join.
- 4. Expand and load: Expand the merged column, choose the manual columns, and click Close & Load to return aligned results to your workbook.
- 5. Refresh when files change: After adding or updating source files, click Data > Refresh All to re-run the append and keep manual data aligned through the key join.
Frequently Asked Questions
Why does manual data become misaligned after a Power Query refresh?
Power Query append combines rows based on the source data order. When new files are added, the row sequence can change, but manual columns outside the query remain in their original positions, so they shift relative to the refreshed results.
What kind of key should I use to merge manual data with Power Query results?
Use a column that is stable and unique for each row, such as a file name, record ID, invoice number, or a composite key made from two fields. Avoid names or dates that may repeat or change.
Can I keep the manual data in the same sheet as the Power Query output?
It is safer to keep it in a separate table or sheet. If you type it directly beside the query output, a refresh can move or reorder the query rows and separate the manual values.
Does WPS Office support Power Query merge operations?
Yes, WPS Spreadsheets includes Power Query tools under the Data tab and the Power Query Editor Home > Merge Queries options, allowing you to join tables by key.
