How to Unpivot Repeating Employee and Share Columns in Power Query
Question details
The user needs to transform a dataset containing repeating columns of employees and their share percentages into a normalized table with one employee/share pair per row.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Normalizing a wide dataset where multiple employee names and share percentages are spread horizontally across repeating columns to prepare it for proper analysis.
- Observed behavior
- Data is currently structured in wide, repeating columns (e.g., employee1, share1, employee2, share2) which is difficult to pivot or aggregate, requiring it to be unpivoted into distinct rows.
Ensure your source data is formatted as an Excel Table and loaded into the Power Query Editor before applying these M code transformations.
Unpivot Dynamic or Repeating Column Patterns
Best for datasets with an unknown or variable number of employee/share column pairs.
This method uses the Unpivot Other Columns feature combined with an Index column to pair adjacent employee and share values dynamically. It is the most robust approach for datasets that frequently change in width.
Select your identifier column (e.g., "Name"), right-click the column header, and choose "Unpivot Other Columns".
Go to the Add Column tab. Add an Index column starting from 0, then add another Index column starting from 1.
Perform a Self-Join (Nested Join) linking the two index columns. This aligns the employee row with its adjacent share row.
Expand the joined table to reveal the share values. Add a Modulo column (divide by 2) to filter out duplicate staggered rows, then remove unnecessary helper columns.
Rename your final paired columns to 'employee' and 'share employee' to finalize the normalization.
Append Tables for a Fixed Number of Pairs
A simpler method if your dataset always has the exact same number of employee and share columns.
Try WPS Office for Powerful Data Analysis and Compatibility
Power Query is a specialized feature of Microsoft Excel. If you are looking for a lightweight, free, and highly compatible alternative to handle your daily spreadsheet tasks, WPS Office is an excellent choice. It offers robust data processing tools, familiar interfaces, and seamless formatting compatibility.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Install the software: Run the installer and follow the on-screen instructions to complete the lightweight installation process.
- 3. Open your Excel files: Launch WPS Spreadsheet and open your existing .xlsx files to instantly resume your data analysis work seamlessly.
Frequently Asked Questions
Why use Power Query instead of standard Excel formulas for unpivoting?
Power Query automates the data transformation process. Once the M code query is built, you can easily refresh it when new data is added, which is much faster and less error-prone than building complex OFFSET or INDEX/MATCH array formulas.
Can I perform unpivoting without writing M code manually?
Yes, many steps can be done using the Power Query graphical interface. For example, selecting your ID column and clicking "Unpivot Other Columns" from the Transform tab automatically generates the underlying M code for the unpivot step.
What if my repeating columns have inconsistent naming conventions?
The dynamic unpivot method (using Modulo and Indexing) relies on the structural order of columns rather than their header names. As long as the employee and share columns appear in adjacent pairs in the source table, the transformation will work correctly.
How do I handle empty cells when unpivoting pairs?
Power Query automatically removes null values during the unpivot process. If you need to keep rows with blank shares, you should replace nulls with zeros or a specific text string before applying the unpivot step.
