Power Query Problems

Combine Sales and Stock Columns by Size in Excel Power Query

Elise WilliamsElise Williams Nov 18, 2026 870 views

Question details

Reshape separate size-specific sales and stock columns into Product Number, Size, Sales, and Stock columns.

Combine Sales and Stock Columns by Size in Excel Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the table in Power Query

Select a cell in the source data in Excel, then choose Data > From Table/Range. Confirm the table range and headers if prompted.

2
Unpivot the size columns

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.

3
Split the headers

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.

4
Pivot the measures

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.

5
Review and load the result

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.

Unpivot and pivot the size columns in Power Query
Check duplicate values: If multiple values exist for the same product, size, and measure, Don't Aggregate can produce an error. Check the source rows or choose a suitable aggregation, such as Sum, in the Pivot Column dialog.
Free Microsoft Office alternative

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.

Review and edit product, size, sales, and stock tables in WPS SpreadsheetsOpen and save Microsoft Excel XLSX and XLS filesNavigate a familiar spreadsheet interfaceUse a free, lightweight Office alternative
microsoft office alternative - wps office

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.