Combine Sales and Stock Columns by Size in Excel Power Query
Question details
Reshape separate size-specific sales and stock columns into Product Number, Size, Sales, and Stock columns.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A table contains a product identifier and separate sales and stock columns for each size.
- Observed behavior
- The desired result is one row per product and size, with sales and stock values in separate columns.
Check that each size-specific header identifies both the measure and size consistently, such as Sales_S and Stock_S. Make sure Product Number identifies the product and check whether each product-size-measure combination is unique.
Unpivot and pivot the size columns in Power Query
Use this method to turn paired size-specific columns into separate Sales and Stock fields.
The steps below assume headers such as Sales_S and Stock_S, where an underscore separates the measure from the size. If your headers use a different pattern, adjust the split step accordingly.
Select a cell in the source data in Excel, then choose Data > From Table/Range. Confirm the table range and headers if prompted.
In Power Query Editor, select Product Number, then choose Transform > Unpivot Columns > Unpivot Other Columns. The original size-specific headers become values in an Attribute column, and their data appears in a Value column.
Select Attribute and choose Transform > Split Column > By Delimiter. Select the delimiter used in your headers, such as an underscore, and split it into a measure column and a size column. Rename them Measure and Size.
Select Measure and choose Transform > Pivot Column. Select Value as the values column and, under Advanced options, choose Don't Aggregate when each product-size-measure combination is unique.
Confirm the columns are Product Number, Size, Sales, and Stock. Set appropriate data types using the column-header type icons, then choose Home > Close & Load.

Work with sales and stock spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and Excel format compatibility for an easy transition. Use WPS Spreadsheets to view and edit your sales and stock tables; the Power Query workflow itself requires Excel.

Frequently Asked Questions
What should I do if my column headers use a space instead of an underscore?
In Power Query Editor, select Attribute and choose Transform > Split Column > By Delimiter. Choose a space as the delimiter and verify that the resulting columns contain the measure and size.
Why does the pivot step show an error?
Don't Aggregate requires a single value for each combination of the remaining columns and measure. Check for duplicate product-size-measure rows, or select an appropriate aggregation such as Sum in the Pivot Column dialog.
How can I keep sizes such as S, M, and L as text?
Select the Size column in Power Query Editor, then use the data type icon in its header to choose Text before loading the result.
Will missing sales or stock values appear in the result?
Unpivoting skips null source values, so a Sales or Stock cell can be null when only the other measure has a value. If both measures are null for a product and size, that combination may not appear as a row.




