Power Query Problems

How to Transform a Multi-Row Excel Table into One Row per ID

Natalie Taylor Oct 22, 2026 868 views

Question details

The user needs to flatten PDF-extracted Excel data that spans multiple rows into a single row per ID, combining parameter names with source column headings.

How to Transform a Multi-Row Excel Table into One Row per ID

Product: Excel
Device & OS: not provided
Scenario: Restructuring structured data extracted from a PDF to prepare it for exporting into a clean, flat CSV file format.
Observed behavior: The source data is spread across multiple rows (e.g., A1:I7) with IDs, separate parameter names, and source headings. It needs to be consolidated into a format containing 42 distinct columns with one row per ID.

Before you start

Ensure your source data is formatted as an Excel Table (Ctrl+T) and clearly identify the columns containing your unique IDs, parameter names, and data values.

Solution 1Recommended

Use Power Query to Unpivot and Pivot Data

Transform the dataset by filling missing values, unpivoting the measurement columns, merging headers, and repivoting to create a single flattened row per ID.

Power Query is the ideal tool for this transformation. By unpivoting the table first, you can dynamically combine the parameter names with the existing column headers. Pivoting the combined column back will then yield the desired single-row format.

Load Data into Power Query

Select any cell within your data range and navigate to the Data tab. Click 'From Table/Range' to open the Power Query Editor.

Fill Missing Values

If your ID column contains blanks for the secondary rows, select the 'ID' column header, right-click, and choose 'Fill' > 'Down' to populate the ID for every row.

Unpivot the Measurement Columns

Select the 'ID' and 'ParameterName' columns. Right-click the headers and select 'Unpivot Other Columns'. This transforms your data into four columns: ID, ParameterName, Attribute (the old column headers), and Value.

Combine Headings and Parameters

Hold the Ctrl key and select both the 'ParameterName' and 'Attribute' columns. Go to the Transform tab and click 'Merge Columns'. Select an appropriate separator (such as an underscore) and name the new column 'MergedHeaders'.

Pivot the Combined Column

Select your new 'MergedHeaders' column. Go to the Transform tab and click 'Pivot Column'. In the dialog box, set the Values Column to 'Value'. Click 'Advanced options' and choose 'Don't Aggregate' as the Aggregate Value Function. Click OK.

Load and Export as CSV

Click 'Close & Load' on the Home tab to output the flattened table to a new worksheet. To export, go to File > Save As, and select 'CSV (Comma delimited)' as the file type.

Use Power Query to Unpivot and Pivot Data

Automation Tip: If you have multiple files formatted this way in a specific folder, you can automate this entire process by selecting Data > Get Data > From File > From Folder.

Free Microsoft Office alternative

Handle Complex Data Easily with WPS Office

While Power Query is a specific feature of Microsoft Excel, WPS Spreadsheet offers powerful built-in data processing tools, advanced PivotTables, and broad format compatibility for managing complex datasets. It serves as a highly capable, free, and lightweight alternative to Microsoft Office, ensuring smooth migration and an intuitive interface.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Lightweight design ensures fast loading and processing of large data tables.Familiar user interface requires no learning curve for Excel users.Built-in advanced PivotTables and data sorting features for easy data consolidation.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting errors when pivoting the combined columns in Power Query?

Errors during the pivot step usually occur if there are duplicate combinations of the ID and your merged column names. Ensure you select 'Don't Aggregate' in the Advanced options of the Pivot Column dialog, and verify that your source data has unique records for each parameter per ID.

How do I refresh the transformed data when my source table updates?

Once you have set up the Power Query transformation, you only need to update your source data table. Navigate to the worksheet containing your flattened results, go to the Data tab, and click 'Refresh All'.

Can I export only the refreshed result as a CSV?

Yes. After loading the flattened Power Query result into a new worksheet, ensure that specific worksheet is active. Navigate to File > Save As, choose your destination folder, and select 'CSV' from the file format dropdown. Only the active sheet is exported.

Excel & Data Problems