Power Query Problems

How to Transform Survey Rows into Question Headers in Excel

Guest Writer Sep 28, 2026 870 views

Question details

The user needs to convert flat survey data where questions and responses are listed in rows into a table format where each question is a column header, keeping respondent IDs and country data intact.

How to Transform Survey Rows into Question Headers in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Restructuring questionnaire or survey data exports into a tabular layout to facilitate easier reporting and data analysis.
Observed behavior: Survey questions and corresponding responses currently occupy individual rows, requiring a transpose or pivot operation to map responses beneath distinct question columns while displaying NA for missing answers.

Before you start

Ensure your survey data is formatted as an Excel Table (press Ctrl+T) and verify if there are any duplicate responses submitted by the same respondent for the same question.

Solution 1Recommended

Use Power Query to Pivot Question Columns

Power Query provides a robust and repeatable way to pivot survey questions into headers while maintaining identifying fields like ID and Country.

This method handles the transposition of rows to columns efficiently and allows you to structure the data perfectly for analysis, automatically filling missing responses with null values.

Load data into Power Query

Select your data table, go to the Data tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.

Merge identifying columns (Optional)

If you have multiple identifying fields, hold Ctrl to select the ID and Country columns, right-click, and choose 'Merge Columns' to create a temporary unique identifier key.

Pivot the Question column

Select the Question column. Navigate to the Transform tab and click 'Pivot Column'.

Configure Pivot values

In the Pivot Column dialog, choose 'Response' as the Values Column. Expand the Advanced options, select 'Don't Aggregate' (or an appropriate aggregation if duplicates exist), and click OK.

Split columns and load

If you merged columns earlier, select the merged column, right-click, and choose 'Split Column' to separate ID and Country. Finally, click 'Close & Load' on the Home tab to output the transformed data into a new worksheet.

Use Power Query to Pivot Question Columns

Handling Duplicates: If a respondent answered the same question twice, selecting an aggregation method like 'Maximum' or 'Count' during the Pivot operation prevents data processing errors.

Solution 2

Transform Data using a Data Model PivotTable

If you prefer not to use Power Query, you can achieve a similar layout using the Excel Data Model and a custom DAX measure.

Free Microsoft Office alternative

Need to Analyze Survey Data? Try WPS Office

While advanced Data Model DAX features are specific to Microsoft Excel, WPS Office offers a free, lightweight, and highly compatible alternative for everyday data analysis, pivot tables, and spreadsheet management. It provides a familiar interface so you can transition seamlessly and manage your datasets with ease.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
2. Install the Software: Run the installer and follow the straightforward on-screen instructions for a quick setup.
3. Open Your Data File: Launch WPS Spreadsheets and open your existing .xlsx survey data files to continue your analysis seamlessly.

Free to use with a lightweight installation packageFully compatible with Microsoft Excel (.xlsx, .csv) formatsBuilt-in PivotTable features for quick survey data summariesFamiliar user interface with zero learning curve for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an error when pivoting survey data in Power Query?

Errors usually occur if there are duplicate responses for the same ID and question. To fix this, go to the Advanced options in the Pivot Column dialog and change the aggregate value function from 'Don't Aggregate' to a specific aggregation like 'Maximum' or 'Count'.

How do I show missing responses as NA instead of blanks?

When you pivot columns in Power Query, any missing intersections are automatically filled with 'null' values. You can select the newly pivoted question columns, click 'Replace Values' in the Transform tab, and replace 'null' with 'NA'.

Can I pivot multiple demographic columns like Age and Gender alongside Country?

Yes. You can hold the Ctrl key to select all your demographic columns (ID, Country, Age, Gender) simultaneously before pivoting. Alternatively, you can temporarily merge them into a single key column, pivot the Question column, and then split the key column back into its original demographic fields.

Excel & Data Problems