Power Query Problems

How to Automatically Split Excel Orders into One Row per Sale Item

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to transform sales data where multiple items are listed in a single order row into a new format where each sale item occupies its own row, while retaining the original invoice number and order details.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Reorganizing multi-item sales data for easier tracking, reporting, and itemized analysis.
Observed behavior: Sales data currently contains up to three items per order on a single row, which prevents detailed item-level reporting and analysis.

Before you start

Ensure your original sales data is formatted as an official Excel Table (by selecting the data and pressing Ctrl+T) and that your column headers clearly distinguish order details from the individual sale items.

Solution 1Recommended

Use Power Query to Unpivot Item Columns

Power Query's Unpivot feature can automatically transpose multiple item columns into individual rows while duplicating the associated order details for each item.

This method is dynamic, meaning once the query is set up, you can simply refresh it whenever new orders are added to your original table without having to repeat the unpivot process.

Load Data to Power Query

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

Select Item Columns

In the Power Query Editor, hold down the Ctrl key and click the column headers for all the sale items you want to split into individual rows.

Unpivot the Selected Columns

Right-click one of the selected item column headers and choose 'Unpivot Only Selected Columns' from the context menu.

Filter Out Blank Items

Click the filter dropdown arrow on the newly created 'Value' column and uncheck '(blank)' or 'null' to remove any empty entries from orders that had fewer than three items.

Load to a New Worksheet

Click 'Close & Load' on the Home tab to export the transformed, itemized data into a brand-new Excel worksheet.

Dynamic Refresh: Whenever you add new sales data to your original table, simply right-click the new output table and select 'Refresh' to automatically apply the split to the new orders.

Organize Data Easily with WPS Spreadsheet

Streamline Your Sales Data Analysis with WPS Office

WPS Spreadsheet provides powerful data manipulation and pivot table features that allow you to easily reorganize and analyze complex sales orders without slowing down your computer.

1. Open Your Sales Data: Launch WPS Spreadsheet and open your .xlsx file containing the sales records.
2. Format as Table: Highlight your dataset and use the 'Format as Table' option in the Home tab to structure your data securely.
3. Utilize PivotTables: Navigate to the 'Insert' tab and insert a PivotTable to dynamically summarize and reshape your itemized sales data.
4. Save and Share: Save your newly organized data in standard Excel format, ensuring complete compatibility with colleagues and clients.

Seamless compatibility with Microsoft Excel (.xlsx) formatsBuilt-in Data formatting and PivotTable features for quick analysisLightweight design ensures fast processing of large sales datasetsFree to use with a highly familiar and intuitive interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I unpivot more than three items per order?

Yes, you can select as many item columns as you need in the Power Query Editor. Just highlight all the relevant item columns before selecting the unpivot option.

Will refreshing the query overwrite manual edits in the output worksheet?

Yes, Power Query replaces the output table data entirely upon refresh. Any manual changes made directly to the generated output worksheet will be lost.

Why did my order numbers disappear after unpivoting?

This usually happens if you accidentally selected the order detail columns instead of the item columns when applying the unpivot transformation. Delete the unpivot step in the 'Applied Steps' pane and ensure you only highlight the item columns.

Excel & Data Problems