Power Query Problems

Convert an Order Items List into Excel Order-Line Rows

Olivia MillerOlivia Miller Nov 18, 2026 869 views

Question details

Convert a cell containing multiple order items into a normalized table with one item per row.

Convert an Order Items List into Excel Order-Line Rows
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
An order report needs separate rows for each item, including its quantity, unit price, and line total.
Observed behavior
Multiple item types and quantities are stored together in one cell, making the orders difficult to report on.
Before you start

Check whether items use a consistent separator, such as a plus sign, and how each quantity and unit price is associated with its item. Identify the Order Number column and confirm that unit prices are available.

Solution 1Recommended

Split consistently formatted order items into rows with Power Query

Use this method when item entries follow a consistent pattern and each item's quantity and price can be identified.

For example, an items cell formatted as `Widget|2|3.50 + Cable|1|5.00` can be split first on `+` and then on `|`. Use your actual separators; do not assume a format if the source data differs.

If quantities or prices are stored separately, match them to each order item using reliable identifiers before calculating totals. Splitting two separate lists into rows independently can produce incorrect combinations.

1
Import the orders

Select the source range, choose Insert > Table, and then select Data > From Table/Range to open it in Power Query.

2
Split items into rows

Select the items column and choose Home > Split Column > By Delimiter. Enter the item separator, such as `+`, expand Advanced options, select Split into Rows, and choose OK.

3
Clean and separate item fields

Select the resulting items column and choose Transform > Format > Trim. If each entry contains item, quantity, and price separated by another delimiter, use Split Column > By Delimiter again to split it into columns, then rename them Item, Quantity, and Unit Price.

4
Calculate line totals

Set Quantity and Unit Price to appropriate numeric types using their column headers. Choose Add Column > Custom Column and enter `[Quantity] * [Unit Price]`; name the new column Line Total.

5
Load and summarize

Choose Home > Close & Load to load the order-line table. To calculate order totals, create a PivotTable from the loaded table, place Order Number in Rows, and place Line Total in Values using Sum.

Split consistently formatted order items into rows with Power Query
Check the matches: Verify several source orders against the output. An order-level quantity or price repeated across split rows is not necessarily the correct value for each item.
Work with order lines in WPS Office

Create an order-line table in WPS Spreadsheets

Use WPS Spreadsheets to enter one item per row, calculate line totals, and sum them by order number.

  1. 1. Open the workbook: Open WPS Spreadsheets and use File > Open to select the order workbook.
  2. 2. Create the order-line table: Add a worksheet and enter Order Number, Item, Quantity, Unit Price, and Line Total as headers in columns A through E.
  3. 3. Enter item details: Enter one item per row, repeating its order number in column A and placing its quantity and unit price in columns C and D.
  4. 4. Calculate line totals: Enter `=C2*D2` in E2 and drag the fill handle down for the remaining rows.
  5. 5. Sum each order: On a summary sheet, enter an order number in A2 and use `=SUMIF(Sheet2!A:A,A2,Sheet2!E:E)` to total its lines. Replace `Sheet2` with the actual order-line sheet name.
Organize order items and quantities into report-ready rowsCalculate line totals and order totals with formulasOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

How do I split a cell into multiple rows in Excel Power Query?

Load the range with Data > From Table/Range, select the items column, and choose Split Column > By Delimiter. Under Advanced options, select Split into Rows.

What if quantities are in a separate cell from the item names?

First determine how each quantity corresponds to an item. Match the values using reliable order and item identifiers; splitting separate lists independently can create incorrect item-quantity combinations.

How do I calculate an order total from the separate rows?

Multiply Quantity by Unit Price for each line, then use Insert > PivotTable to group by Order Number and sum Line Total. You can also use SUMIF against the order-number and line-total columns.

Will splitting an item list automatically find unit prices?

No. Power Query can separate prices already present in a consistent item entry, but prices stored elsewhere must be matched to the correct items before line totals can be calculated.