Power Query Problems

How to Automate Reorganizing an Excel Table with Power Query

Partner Editor Oct 8, 2026 868 views

Question details

The user needs a method to automate the conversion of a wide Excel table into a reorganized, longer format, as manual transformations become highly inefficient for growing datasets.

How to Automate Reorganizing an Excel Table with Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Transforming and reshaping large datasets from a horizontal wide format into a structured vertical list with tags and value columns.
Observed behavior: Currently, reorganizing the table is done manually, which is time-consuming and does not update automatically when new data is added to the source.

Before you start

Ensure your source dataset is formatted as an official Excel Table by selecting your data and pressing Ctrl+T, and verify that all column headers are clearly labeled.

Solution 1Recommended

Use Power Query to Unpivot and Reorganize Data

This method utilizes the 'Unpivot Other Columns' feature in Power Query to automatically reshape your wide table into a structured format that updates with a single click.

Power Query, also known as Get & Transform Data, is an advanced data shaping tool built into Excel. It allows you to record transformation steps such as unpivoting columns, which shifts wide data into rows while duplicating the necessary identifier tags.

Load Table into Power Query

Click anywhere inside your source table (Table 1). Go to the Data tab on the Excel ribbon and click 'From Table/Range'. This will open the Power Query Editor window.

Select the Columns to Keep Static

In the Power Query Editor, locate the identifier columns that you do not want to unpivot (for example, Location and Facility). Hold down the Ctrl key and click on each of these column headers to select them.

Unpivot Other Columns

Right-click on one of the highlighted column headers and select 'Unpivot Other Columns' from the context menu. This action will immediately collapse the unselected columns into two new columns: 'Attribute' (former headers) and 'Value' (the data).

Rename and Load the Transformed Data

Double-click the new 'Attribute' and 'Value' column headers to rename them to something meaningful for your dataset. Finally, click the 'Close & Load' button in the Home tab to output this reorganized data into a new worksheet as Table 2.

Use Power Query to Unpivot and Reorganize Data

Automated Updates: Once configured, you never have to repeat these steps. When you add new rows or columns to the original Table 1, simply right-click Table 2 and select 'Refresh' to instantly apply the reorganization.

Free Microsoft Office alternative

Looking for a Free, Powerful Alternative to Excel?

While Power Query is a specific feature within Microsoft Excel, WPS Office provides a robust, free spreadsheet application equipped with extensive data analysis, pivot tables, and formatting tools. It offers a familiar interface and seamless compatibility for handling complex datasets without the high subscription costs.

1. Download and Install: Visit the official WPS website to download WPS Office for free and follow the standard installation process.
2. Open Your Spreadsheets: Launch WPS Spreadsheets and directly open your existing .xlsx files without worrying about formatting loss.
3. Analyze Your Data: Use the Data tab to access powerful sorting, filtering, and PivotTable tools to reorganize and analyze your datasets effectively.

100% format compatibility with Microsoft Excel (.xlsx) filesLightweight application that runs smoothly on older devicesFamiliar user interface that requires zero learning curveComprehensive built-in tools for data visualization and pivot table analysis

microsoft office alternative - wps office

Frequently Asked Questions

What does 'Unpivot' mean in data transformation?

Unpivoting is the process of rotating data from a wide column-based format into a longer row-based format. It creates attribute-value pairs, making the dataset much easier to analyze using PivotTables or database queries.

Do I need to recreate the Power Query steps if I add more data?

No. The primary advantage of Power Query is automation. As long as your new data is added to the original source table, you can simply right-click the transformed output table and click 'Refresh' to run all steps automatically.

Why is the 'From Table/Range' button greyed out?

This option typically becomes greyed out if your active cell is not placed within a dataset, or if you are currently double-clicked inside a cell (edit mode). Press 'Enter' or 'Esc' to exit edit mode, then click once on a cell inside your table.

Excel & Data Problems