Power Query Problems

How to Transform Monthly Columns into Rows using Excel Power Query

Olivia Miller Sep 30, 2026 869 views

Question details

The user needs to reshape a wide dataset by converting multiple monthly columns into rows while retaining fixed identifying fields.

Transform Monthly Columns into Rows with Excel Power Query

Product: Excel
Device & OS: not provided
Scenario: Restructuring monthly datasets into a long, database-friendly format for easier analysis and pivot table creation.
Observed behavior: Monthly data needs to be unpivoted so that each month-year and amount combination becomes a new row associated with its original serial number, description, and PO number.

Before you start

Ensure your source dataset is formatted as a formal Excel Table and that all identifying columns have clear, non-empty headers before importing into Power Query.

Solution 1Recommended

Use Unpivot Columns in Power Query Editor

Import your table into Power Query and use the Unpivot feature to flatten your monthly data into dedicated Month-Year and Amount columns.

Power Query provides a built-in feature to unpivot wide data. This transforms multiple columns of similar data into attribute-value pairs, making it much easier to analyze using PivotTables or external database tools.

Load data into Power Query

Select any cell inside your source table and go to Data > From Table/Range on the Excel ribbon to open the Power Query Editor.

Select the monthly columns

In the Power Query Editor, hold down the Ctrl or Shift key and click the headers of the monthly columns (e.g., January through December) that you want to transform.

Unpivot the selected columns

Navigate to the Transform tab and click Unpivot Columns. Alternatively, you can right-click one of the selected headers and choose Unpivot Columns from the context menu.

Rename the new columns

Double-click the header of the new Attribute column and rename it to Month-Year. Then, double-click the Value column header and rename it to Amount.

Load the transformed data

Go to the Home tab and select Close & Load. This will output your unpivoted data into a new Excel worksheet.

Use Unpivot Columns in Power Query Editor

Dynamic Updates: The output remains connected to your original source table. Whenever the source data is modified, simply click Data > Refresh All in Excel to update your transformed rows instantly.

Free Microsoft Office alternative

Need a lightweight and compatible spreadsheet tool? Try WPS Office

If you are looking for a reliable, fast, and free alternative to Microsoft Office, WPS Office offers excellent compatibility with Excel formats. It includes powerful built-in data analysis tools, PivotTables, and a familiar interface that makes handling complex spreadsheets a breeze without heavy subscription costs.

Free to use with a fast and lightweight installation processHighly compatible with Microsoft Excel (.xlsx, .csv) formatsProcess wide datasets easily with advanced built-in spreadsheet and PivotTable toolsIntuitive and familiar interface requires zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

How do I update my unpivoted data when the source table changes?

Because Power Query maintains a connection to your original data, you only need to go to the Data tab in Excel and click Refresh All. The query will run automatically and update your output table with the new monthly data.

Should I use 'Unpivot Columns' or 'Unpivot Other Columns'?

Using Unpivot Columns is best if the number of monthly columns is strictly fixed. If you anticipate adding new columns later (like upcoming months), select your fixed identifying columns (like S.No. and Description), right-click, and choose Unpivot Other Columns. This ensures any new columns added to the source table in the future are automatically unpivoted.

Does Power Query delete or change my original source data?

No. Power Query only reads from your source table. All transformations happen within the editor, and the final result is loaded into a completely separate worksheet, leaving your original data untouched.

Excel & Data Problems