How to Combine Top Three Rows into Power Query Headers
Question details
The user needs to consolidate header information that is currently spread across the first three rows of a dataset into a single header row for each column using Power Query.

- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Organizing and cleaning complex spreadsheet tables where headers span multiple rows before data analysis.
- Observed behavior
- The header information is distributed across three separate rows instead of a unified single header row, complicating downstream data processing.
Ensure your dataset is loaded into the Power Query Editor and check the 'Applied Steps' pane to remove any automatic 'Promoted Headers' step if Power Query has already mistakenly promoted the first row.
Combine Multiple Header Rows Using Transpose and Merge
This method flips the data layout so you can easily merge the text from the top three rows into a single string before setting it as the new header.
Power Query does not have a direct button to merge rows. By transposing the table, the rows become columns, allowing you to use the 'Merge Columns' feature. Once merged, you can flip the data back to its original layout.
If Power Query automatically promoted your first row, navigate to the 'Transform' tab and click 'Use Headers as First Row' so all header data is within the table body.
Go to the 'Transform' tab and click the 'Transpose' button. This will rotate your entire table, turning your original top three rows into the first three columns.
Hold the Ctrl key and select the first three columns. Right-click the column headers and select 'Merge Columns'. Choose a separator (like a space or underscore) and click 'OK'.
Click the 'Transpose' button again on the 'Transform' tab to return your data to its original vertical orientation.
Navigate to the 'Home' or 'Transform' tab and click 'Use First Row as Headers'. Your previously spread out text is now combined into a single, unified column header.

Manage Your Spreadsheet Data Easily with WPS Office
While advanced Power Query transformations are specific to Microsoft Excel, WPS Office provides a lightweight, highly compatible alternative for everyday data processing, formatting, and pivot table analysis.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite to your computer.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx file without losing any standard formatting.
- 3. Analyze Your Data: Use built-in data sorting, filtering, and pivot tables to organize and summarize your datasets efficiently.

Frequently Asked Questions
Can I combine more than three header rows using this method?
Yes, you can combine any number of header rows. Simply select the corresponding number of columns after transposing your dataset and merge them before transposing the table back.
What happens if my original columns contained blank cells in the header rows?
When merging the columns in Power Query, blank (null) cells will be ignored or merged as empty spaces depending on the separator you choose. You may want to use a 'Replace Values' step to replace nulls with blanks if you require a cleaner header string.
Why did Power Query automatically promote my first row before I started?
Power Query often applies a 'Promote Headers' step automatically based on its initial data type detection. You can easily delete this step from the 'Applied Steps' pane on the right-hand side before starting the transpose process.




