Power Query Problems

How to Split Multiple Columns into Separate Rows in Power Query

Olivia Miller Sep 30, 2026 869 views

Question details

The user needs to transform spreadsheet data so that multiple product columns within a single transaction are placed into separate rows, repeating the associated customer and transaction details for each product.

How to Split Multiple Columns into Separate Rows using Power Query

Product: Spreadsheets
Device & OS: not provided
Scenario: Reorganizing transaction data containing multiple horizontal product columns into a vertical, row-based format for easier analysis.
Observed behavior: Data is currently spread horizontally across multiple product columns per transaction, requiring transformation into a flat list with one product per row.

Before you start

Ensure your source dataset is formatted as a standard Table and that there are no merged cells or blank header rows before importing it into the data editor.

Solution 1Recommended

Use the Unpivot Feature in Power Query

The most efficient and dynamic method to split multiple columns into individual rows is by using the Unpivot Columns feature in Power Query.

Unpivoting automatically transforms wide data (multiple columns) into long data (multiple rows). When you unpivot specific columns, Power Query intuitively duplicates the remaining row data (like Customer ID and Date) to align with each new row generated.

Load data to Power Query

Select any cell inside your Excel table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Select the product columns

In the Power Query Editor, locate Product 1, Product 2, and Product 3. Hold down the Ctrl key and click each column header to select them simultaneously.

Unpivot the selected columns

Go to the Transform tab and click 'Unpivot Columns'. Alternatively, right-click any of the selected column headers and choose 'Unpivot Only Selected Columns'.

Clean and rename data

The unpivot action creates two new columns: 'Attribute' (containing the old column names) and 'Value' (containing the products). Right-click the 'Attribute' column and select 'Remove'. Double-click the 'Value' column header and rename it to 'Product'. Click the drop-down arrow on the 'Product' column and uncheck any blank or null values to filter them out.

Load the transformed data

Navigate to the Home tab and click 'Close & Load' to output the transformed, row-based dataset back into a new worksheet.

Data Integrity Maintained: Transaction details like Customer ID, location, and date will automatically repeat for each new product row, keeping your transaction records accurate and ready for pivot table analysis.

Solution 2

Use Advanced Formulas to Reorganize Data

If you cannot use Power Query, you can achieve similar results using dynamic array formulas, though it requires a more manual setup.

Free Microsoft Office alternative

Analyze and Manage Complex Data with WPS Office

If you are dealing with complex data transformations and need a highly compatible, reliable spreadsheet solution, WPS Office is an excellent alternative. It natively supports Microsoft Excel formats, offering powerful data handling, Pivot Tables, and a familiar user interface without heavy subscription costs.

Seamless compatibility with Microsoft Excel formats (.xlsx, .xls, .csv)Includes powerful Pivot Tables and advanced data filtering toolsLightweight installation that runs smoothly even with large datasetsFree to use with a highly intuitive, easy-to-navigate interface

microsoft office alternative - wps office

Frequently Asked Questions

Why did Unpivot Columns create blank rows in my data?

If your original product columns contained empty cells, unpivoting will generate rows with null or blank values. You can easily fix this by clicking the drop-down filter arrow on the newly unpivoted column and unchecking the blank or null options before loading the data.

Should I unpivot the product columns or the transaction details columns?

You should focus on the columns that contain the repeated horizontal data (the products). A best practice is to select the transaction details columns you want to keep (like ID and Date), right-click their headers, and choose 'Unpivot Other Columns'. This ensures that if a 'Product 4' is added later, it will be unpivoted automatically.

Can I undo the Unpivot step if I make a mistake?

Yes. In the Power Query Editor, look at the 'Applied Steps' pane on the right side of the window. Simply click the 'X' icon next to the Unpivot step to delete it and instantly revert your data to the previous state.

Excel & Data Problems