Why WPS Office Power Query Adds Numbers to Duplicate Column Names
Question details
The user wants to understand why Power Query appends numeric suffixes to duplicate column names such as Production capacity1 and Production capacity2, and how to resolve or work with this behavior.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working in Power Query in WPS Office Spreadsheets and has repeated column headings such as Production capacity.
- Observed behavior
- Power Query automatically appends numbers to duplicate column headings, such as Production capacity1 and Production capacity2, because spreadsheet tables require every column name to be unique.
Open the workbook in WPS Office Spreadsheets and launch the Power Query Editor from the Data tab. In Query Settings, check the Applied Steps so you can see exactly where duplicate headings are created before renaming or reshaping the query.
Rename duplicate column headings to unique names in Power Query
Use this when you want to keep the current column layout but need clear, meaningful field names without numeric suffixes.
This behavior is by design: when Power Query outputs to a spreadsheet table, every column name must be unique. Renaming the duplicates keeps the table valid and makes formulas or PivotTables easier to read.
In WPS Office Spreadsheets, click Data > Get Data and open the Power Query Editor for the table that contains duplicate column headings.
Right-click the column header that shows a number, such as Production capacity1.
Choose Rename, type a meaningful unique name such as Production capacity North, and press Enter.
Repeat for each duplicate, then click Close & Load to return the unique column names to the worksheet.
Reshape repeated categories into row values instead of duplicate columns
Use this when the repeated columns represent the same kind of data for different categories, such as capacity by region or month, and a normalized layout is better for summaries.
Review the Power Query steps that create or expand duplicate headers
Use this to find the exact step that introduces duplicate column names and decide whether to rename, unpivot, or edit that step.
Fix Duplicate Column Names in Power Query with WPS Office Spreadsheets
WPS Office Spreadsheets gives you the Power Query tools to rename duplicate column headings or unpivot repeated categories into row values before loading, so your data remains clean and usable.
- 1. Open Power Query: Open the workbook in WPS Office Spreadsheets and launch Power Query Editor from the Data tab.
- 2. Review Applied Steps: Check Query Settings > Applied Steps to find where Promoted Headers or Expanded Table created duplicate names.
- 3. Rename or unpivot: Right-click duplicate headers to rename them, or select the duplicate columns and choose Unpivot Columns to move categories into rows.
- 4. Close and load: Click Close & Load to write the cleaned unique column structure back to the worksheet.
Frequently Asked Questions
Is Power Query adding numbers to duplicate column names a bug?
No. Spreadsheet tables require every column name to be unique, so Power Query automatically appends numbers to repeated headings like Production capacity1. Check the Applied Steps in Power Query Editor to see where the suffixes were added.
How do I rename a duplicate column in the Power Query Editor?
Open the Power Query Editor from the Data tab in WPS Office Spreadsheets, right-click the duplicate header, choose Rename, type a unique name, press Enter, and then click Close & Load.
Can I prevent Power Query from adding numeric suffixes without renaming columns?
You cannot turn off the uniqueness requirement for table output, but you can insert a rename step after the step that creates duplicate headers, or unpivot duplicate columns so repeated categories become row values.
How do I move duplicate categories into rows instead of keeping numbered columns?
Select the duplicate columns in the Power Query Editor, right-click and choose Unpivot Columns, rename the resulting Attribute and Value columns, and click Close & Load. You can then summarize the normalized table with a PivotTable.
What should I check before renaming or unpivoting duplicate columns?
In Query Settings, click through the Applied Steps, especially Promoted Headers and Expanded Table, so you can identify exactly which step creates the duplicate column names.
