How to Expand Excel Quantities into Individual Rows with Power Query
Question details
The user wants to duplicate rows based on a quantity value, creating one individual row for each unit, and have the result update automatically when source data changes.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Organizing inventory, ticketing, or itemized lists where grouped quantities need to be expanded into single-unit rows.
- Observed behavior
- The user currently has grouped data (one row with a quantity of N) and needs N individual rows, each with a quantity of 1 for that item.
Ensure your source data is formatted as an Excel Table (by selecting the data and pressing Ctrl+T). This allows Power Query to easily load and refresh the data dynamically.
Expand Quantities Using Power Query M Code
Use Power Query's custom list generation to dynamically expand rows for each unit.
This method uses Power Query to create a sequential list based on the item's quantity number, expands that list into new rows, and then replaces the original quantity column with a fixed value of 1.
Select any cell in your source data table, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
Go to the 'Add Column' tab and click 'Custom Column'. Name the new column 'Unit' and enter the formula: ={1..Number.From([Quantity])}. Click OK.
Click the expand icon (two diverging arrows) at the top right of the new 'Unit' column header and select 'Expand to New Rows'.
Select the original 'Quantity' column and the 'Unit' helper column. Right-click the column headers and choose 'Remove Columns'.
Go to 'Add Column' > 'Custom Column', name it 'Quantity', and enter the number 1. Set its data type to Whole Number, then click 'Home' > 'Close & Load' to return the expanded data to Excel.
Experience Seamless Excel Compatibility with WPS Office
While Power Query is a specific tool within Microsoft Excel, WPS Office provides a lightweight, highly compatible, and free alternative for managing complex spreadsheets, everyday data analysis, and advanced charting without the heavy resource footprint.
- 1. Download and Install: Visit the official WPS Office website to download and install the free suite on your device.
- 2. Open Your Workbook: Launch WPS Spreadsheets and open your existing .xlsx workbook to instantly view your data.
- 3. Analyze and Manage Data: Use WPS Office's intuitive data management tools, PivotTables, and functions to organize and analyze your lists seamlessly.
Frequently Asked Questions
Why am I getting an error when entering the custom column formula?
Ensure you use the exact syntax ={1..Number.From([Quantity])}. Power Query is case-sensitive, so using lowercase like 'number.from' or 'quantity' (if your actual column is capitalized) will result in an error.
How do I update the result when my original data changes?
Simply add, edit, or remove data in your original source table. Then, navigate to the 'Data' tab in the Excel ribbon and click 'Refresh All'. The query will run again and automatically adjust the expanded rows.
Can I use VBA instead of Power Query to expand rows by quantity?
Yes, a VBA macro can be written to loop through rows and duplicate them based on the quantity value. However, Power Query is often preferred because it automatically updates on refresh without the security warnings or complexities associated with macro-enabled workbooks.
