Power Query Problems

How to Expand Excel Rows Based on Quantity Using Power Query

Rana Garcia Sep 27, 2026 869 views

Question details

The user needs to duplicate items in a dataset into one row per unit based on a quantity column, assigning each new row a quantity of 1.

How to Expand Excel Rows Based on Quantity Using Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Transforming and reshaping a condensed dataset where a single row with a quantity greater than 1 must be split into multiple individual rows for detailed tracking.
Observed behavior: Data is currently grouped with an aggregate quantity value in a single row per item. The desired output is multiple rows per item, each reflecting a unit quantity of 1.

Before you start

Ensure your original dataset is formatted as an Excel Table (press Ctrl+T) and that you have a specific numerical column representing the quantity to base the row expansion on.

Solution 1Recommended

Expand Rows Using Power Query

Use Power Query to generate a list based on the quantity value, expand the list into new rows, and adjust the output quantity to 1.

Load Data into Power Query

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

Create a Custom List Column

Go to the Add Column tab and click 'Custom Column'. In the Custom Column formula box, type ={1..[Quantity]} (assuming your column is named 'Quantity') to create a list from 1 up to the quantity number.

Expand the List to New Rows

Click the expand icon (two diverging arrows) located in the header of your new Custom column, and select 'Expand to New Rows'.

Assign a Unit Quantity of 1

Go to Add Column > Custom Column again, name this new column 'Qty', and enter 1 in the formula box to assign a unit value to each newly expanded row.

Clean Up and Load Data

Right-click the original 'Quantity' column and the first 'Custom' helper column, select 'Remove Columns', then go to the Home tab and click 'Close & Load' to return the expanded data to your worksheet.

Solution 2

Expand Rows Using Dynamic Array Formulas

For users who prefer worksheet formulas over Power Query, the REDUCE and LAMBDA functions can dynamically expand rows based on quantities.

Expand Rows Based on Quantity in WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array formulas, allowing you to quickly expand rows based on quantity directly in the worksheet without navigating complex external query setups.

1. Prepare Your Dataset: Open your worksheet in WPS Spreadsheet, ensuring your items and quantity columns are clearly labeled and contain continuous data.
2. Enter the Combination Formula: Select a blank cell and input the dynamic array formula combining REDUCE, LAMBDA, and SEQUENCE functions to define the row expansion logic.
3. Adjust Range References: Modify the formula's array ranges to accurately reflect your data columns (e.g., selecting the exact item names range and the corresponding quantity values range).
4. Apply and Spill the Results: Press Enter to execute the formula. WPS Spreadsheet will automatically spill the duplicated rows and assign a unit quantity of 1 to each item instantly.

Seamlessly handles advanced dynamic array formulas like REDUCE, LAMBDA, and SEQUENCE.Highly compatible with Microsoft Excel (.xlsx) workbooks and formula structures.Lightweight software with powerful data transformation tools suitable for heavy datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Can I expand rows based on quantity without using Power Query?

Yes, you can use dynamic array formulas in newer versions of Excel or WPS Spreadsheet. Functions like REDUCE, LAMBDA, and SEQUENCE can be nested together to dynamically split and duplicate rows based on a specified quantity column directly in the worksheet.

Why is my Power Query 'Expand to New Rows' option grayed out?

The 'Expand to New Rows' option is only available for columns that contain nested lists or tables. Ensure you have correctly created a custom column with the formula ={1..[Quantity]} to generate a valid list type before attempting to expand.

Does the expanded data automatically update if I change the original quantity?

If you use dynamic array formulas, the expanded results will update instantly when source values change. If you use the Power Query method, you must right-click the resulting expanded table in your worksheet and select 'Refresh' to apply the latest changes from your source data.

Excel & Data Problems