Power Query Problems

How to Convert Repeating Student Subjects into a Matrix in Excel

Anum MushtaqAnum Mushtaq Nov 10, 2026 868 views

Question details

The user needs to convert a two-column list containing student names and their subjects into a matrix format where each student occupies a single row and their corresponding subjects are spread across separate columns.

Convert Repeating Student Subjects into an Excel Matrix
Product
Microsoft Excel
Device & OS
not provided
Scenario
Transforming repeating tabular data into a flattened horizontal matrix for easier reading, reporting, or analysis.
Observed behavior
The user wants to reshape vertical, repeating data (Student, Subject) into a consolidated horizontal matrix without losing data relationships, but native functions may throw nested array errors.
Before you start

Ensure your data is formatted as an official Excel Table (press Ctrl+T) before starting. This makes it easier to reference the data dynamically in Power Query and prevents issues when new rows are added.

Solution 1Recommended

Use Power Query to Group, Transpose, and Expand Data

Power Query is the most reliable method for pivoting this type of text-based data without running into nested array limitations.

By grouping the data by student name and leveraging custom M code to transpose the subject columns, Power Query can dynamically generate a matrix regardless of how many subjects a student has.

1
Load Data into Power Query

Select any cell inside your data table, go to the Data tab on the ribbon, and click 'From Table/Range' to launch the Power Query Editor.

2
Group by Student Name

Select the 'Name' column, navigate to the Home tab, and click 'Group By'. In the dialog, group by Name, set the New column name to 'DATA', and choose 'All Rows' as the operation.

3
Transpose the Subjects

Open the Advanced Editor or modify the formula bar for the Grouped step. Change the 'DATA' column logic to transpose the subjects: Table.Transpose(Table.SelectColumns(_, {"Subject"})).

4
Expand the Columns

Click the Expand icon (two diverging arrows) on the header of the new 'DATA' column. Select all dynamically generated columns to extract the transposed subjects.

5
Load the Matrix to Excel

Once the data looks correct, click 'Close & Load' on the Home tab to output the newly formatted matrix into a new Excel worksheet.

Use Power Query to Group, Transpose, and Expand Data
Dynamic Expansion: Power Query will automatically create enough columns to accommodate the student with the maximum number of subjects whenever you refresh the data.
Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

If you are dealing with complex nested array limitations or want to avoid expensive Office 365 subscriptions, consider switching to WPS Office. It provides a lightweight, highly compatible alternative with powerful data processing capabilities.

  1. 1. Download and Install: Visit the official WPS Office website to download and install the free suite on your device.
  2. 2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx workbook directly without formatting loss.
  3. 3. Analyze Your Data: Utilize WPS Spreadsheets' robust formula library and built-in features to easily pivot, transpose, and organize your datasets.
Completely free to use with a familiar, intuitive interface.High compatibility with Microsoft Excel (.xlsx) files and standard array formulas.Built-in powerful data analysis tools to organize and pivot your data easily.Lightweight software that runs smoothly on Windows, Mac, Linux, iOS, and Android.
microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'Nested arrays are not supported' error in Excel?

This error occurs in modern Excel when dynamic array functions (like MAP) attempt to return an array of arrays—for example, trying to spill multiple columns for a single mapped row. Creating a custom LAMBDA helper function is usually required to resolve this.

Can I convert this list into a matrix using a standard PivotTable?

A standard PivotTable summarizes numeric data effectively, but it is not designed to concatenate or display multiple text values (like subject names) across separate columns for a single row without relying on the Data Model and DAX measures.

Does Power Query update automatically when my original data changes?

No, Power Query does not update instantly as you type. To see the updated matrix after altering your source data, right-click anywhere inside the loaded Power Query matrix table and select 'Refresh', or go to the Data tab and click 'Refresh All'.