How to Subtract Values Between Dynamic Power Query Tables in WPS Office
Question details
The user needs to subtract a parameter from every numeric value or compare maximum and minimum Power Query results when table columns and rows change dynamically.
- Product
- WPS Office Spreadsheets - Power Query
- Device & OS
- not provided
- Scenario
- The user works with dynamic Power Query tables whose columns and rows are updated regularly and needs to apply parameter subtraction or compare maximum and minimum values without rewriting queries manually.
- Observed behavior
- Numeric values should be subtracted by a parameter or maximum and minimum comparisons should remain correct when new columns, fruits, or time periods are added.
Open WPS Office Spreadsheets and load the workbook that contains the Power Query tables. Make sure the parameter is defined as a query or named value, and confirm that identifying columns such as Fruit and Period are consistent between the queries you plan to merge.
Subtract a Parameter from Every Numeric Value Using Unpivot and Pivot in WPS Power Query
Use this solution when numeric columns may be added, removed, or renamed and you need the same parameter subtracted from every value column without hard-coding column names.
Unpivoting keeps label columns separate from numeric data, so the subtraction step only needs to reference one value column. After subtracting the parameter, you can pivot the data back to the original layout.
In WPS Spreadsheets, select Data > Get Data > From Table/Range to create a query, or right-click an existing query and choose Edit to open Power Query Editor.
Select the identifying columns such as Fruit and Period, right-click one of the selected column headers, and choose Unpivot Other Columns.
Select the Value column, go to Transform > Standard > Subtract, and enter the parameter value or select the parameter query you prepared.
Select the Attribute column, choose Transform > Pivot Column, set the Values Column to the subtracted Value column, and then select Close & Load to return the result to the worksheet.
Dynamically Compare Maximum and Minimum Power Query Results by Merging Queries
Use this solution when you have separate maximum and minimum result queries and need row-level differences that update as new identifiers are added.
Subtract Dynamic Table Values in WPS Office Power Query
Use Power Query Editor in WPS Office Spreadsheets to unpivot numeric columns, subtract a parameter or compare maximum and minimum queries, and then pivot or merge the results back. The query steps stay dynamic for new rows and columns without hard-coded column names.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the workbook that contains the Power Query tables.
- 2. Start Power Query Editor: Go to Data > Get Data > From Table/Range, or edit an existing query to open Power Query Editor.
- 3. Prepare the dynamic value structure: Select the label columns, right-click a selected header, and choose Unpivot Other Columns so all numeric values are placed in a single Value column.
- 4. Apply the subtraction: Select the Value column, go to Transform > Standard > Subtract, and enter the parameter or select the parameter query.
- 5. Restore and load: Use Transform > Pivot Column to reshape the data, then click Close & Load to return the dynamic result to the worksheet.
Frequently Asked Questions
How do I subtract the same parameter from many numeric columns in Power Query?
Select the identifier columns in Power Query Editor, right-click and choose Unpivot Other Columns, apply Transform > Standard > Subtract to the resulting Value column, and then pivot the Attribute column back to restore the layout.
Can WPS Power Query compare maximum and minimum tables when new rows are added?
Yes. Merge the maximum and minimum queries by common identifying columns such as Fruit and Period, expand the numeric value from the second query, and subtract the minimum value from the maximum value. New rows appear after the query is refreshed.
Why should I unpivot before subtracting instead of selecting every column?
Unpivoting creates one value column, so the subtraction step does not depend on column names. When new numeric columns are added later, they are included automatically without changing the query steps.
Does this method work with .xlsx Excel Power Query files?
Yes. WPS Office Spreadsheets supports Power Query workflows and .xlsx files, so you can apply the same unpivot, merge, and subtraction steps to Excel-compatible workbooks.
What should I check before merging maximum and minimum queries?
Confirm that the identifying columns have the same data types in both queries and contain unique combinations. Duplicate keys can create unwanted row multiplication and incorrect differences.
