Efficient Way to Pivot Table with Duplicate Subjects per Student in Power Query

[Suraj_Ncircle]

I have a dataset with columns: Student, Subject, and Score. I want to pivot it so that each subject becomes a separate column. The challenge is that a student can have the same subject multiple times (e.g., repeated assessments), and I want each occurrence to become a separate column (e.g., Math, Math_2, etc.), keeping each student on a single row.

I’ve achieved this using Table.Group, but it’s slow and inefficient with large datasets (30,000+ students, each with 10+ subjects). The current method involves checking for duplicate subjects per student, which is time-consuming.

Is there a more efficient way to perform this transformation in Power Query or M—perhaps by dynamically handling duplicates during the pivot step?

Any suggestions for improving performance would be greatly appreciated.

Example Dataset

Students	Subject	Score
John	Maths	80
John	Maths	85
John	English	78
Alice	Maths	90
Alice	Science	88
Alice	Maths	92
Bob	English	70
Bob	English	75
Bob	Science	85

Desired result

Student	Maths	Maths_2	English	English_2	Science
John	80	85	78
Alice	90	92			88
Bod			70	75	85

let
    Source = Excel.Workbook(File.Contents("C:\Duplicate students.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    AddIndex = Table.AddIndexColumn(#"Promoted Headers", "Index", 1, 1, Int64.Type),
    AddGroupedIndex = Table.Group(AddIndex, {"Student", "Subject"}, {
        {"AllRows", each Table.AddIndexColumn(_, "SubIndex", 1, 1, Int64.Type)}
    }),
    #"Removed Other Columns" = Table.SelectColumns(AddGroupedIndex,{"AllRows"}),
    ExpandRows = Table.Combine(#"Removed Other Columns"[AllRows]),
    #"Removed Columns" = Table.RemoveColumns(ExpandRows,{"Index"}),
    ReplaceValue = Table.ReplaceValue(#"Removed Columns", 1, "", Replacer.ReplaceValue, {"SubIndex"}),
    Custom1 = Table.AddColumn(ReplaceValue, "newSubject", each [Subject] & Text.From([SubIndex])),
    #"Removed Columns1" = Table.RemoveColumns(Custom1,{"Subject", "SubIndex"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[newSubject]), "newSubject", "Score", List.Sum)
in
    #"Pivoted Column"

[johnbasha33 · Accepted solution]

@Suraj_Ncircle 

let
// Step 1: Load your base table
Source = Excel.Workbook(File.Contents("C:\Duplicate students.xlsx"), null, true),
Sheet1 = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Headers = Table.PromoteHeaders(Sheet1, [PromoteAllScalars=true]),

// Step 2: Add index per Student+Subject pair for uniqueness
AddIndexPerStudentSubject = Table.Group(
Headers, {"Student", "Subject"},
{{"Data", each Table.AddIndexColumn(_, "SubjectIndex", 1, 1), type table [Student=nullable text, Subject=nullable text, Score=nullable number, SubjectIndex=number]}}
),
Flattened = Table.Combine(AddIndexPerStudentSubject[Data]),

// Step 3: Create unique column name like "Maths", "Maths_2", etc.
AddSubjectIndexSuffix = Table.AddColumn(Flattened, "SubjectKey", each
if [SubjectIndex] = 1 then [Subject] else [Subject] & "_" & Text.From([SubjectIndex])
),

// Step 4: Remove unnecessary columns and prepare for pivot
Cleanup = Table.SelectColumns(AddSubjectIndexSuffix, {"Student", "SubjectKey", "Score"}),

// Step 5: Pivot
Pivoted = Table.Pivot(Cleanup, List.Distinct(Cleanup[SubjectKey]), "SubjectKey", "Score", List.Sum)
in
Pivoted

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    pivot = Table.Buffer(Table.Pivot(Source, List.Distinct(Source[Subject]), "Subject", "Score", (x) => x)),
    split = List.Accumulate(
        List.Skip(Table.ColumnNames(pivot)), 
        pivot, 
        (s, c) => Table.SplitColumn(s, c, (x) => x, List.Max(List.Transform(Table.Column(pivot, c), List.Count)))
    )
in
    split

[v-hjannapu]

Hi @Suraj_Ncircle,

I would also take a moment to thank @Omid_Motamedise  and @johnbasha33  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Harshitha.

[v-hjannapu]

Hi @Suraj_Ncircle,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Regards,
Harshitha.

[v-hjannapu]

Hello @Suraj_Ncircle,
I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[Omid_Motamedise · Accepted solution]

Hi @Suraj_Ncircle 

Just copy the following formula and paste it into the Advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUfJNLMkoBtIWBkqxOpiipsiirnnpOZnFGUCWuQVY3DEnMzkVSbmlAYpwcHJmah6YZYFDvRFY2Ck/CdV0A+zCpkjCSGYDhWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Students = _t, Subject = _t, Score = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Students", "Subject"}, {{"Count", each Table.AddIndexColumn(_,"Index",1)}})[[Count]],
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Students", "Subject", "Score", "Index"}, {"Students", "Subject", "Score", "Index"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Count", {{"Index", type text}}, "en-AU"),{"Subject", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Score", each try _{0} otherwise "")
in
    #"Pivoted Column"

[johnbasha33 · Accepted solution]

@Suraj_Ncircle 

let
// Step 1: Load your base table
Source = Excel.Workbook(File.Contents("C:\Duplicate students.xlsx"), null, true),
Sheet1 = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Headers = Table.PromoteHeaders(Sheet1, [PromoteAllScalars=true]),

// Step 2: Add index per Student+Subject pair for uniqueness
AddIndexPerStudentSubject = Table.Group(
Headers, {"Student", "Subject"},
{{"Data", each Table.AddIndexColumn(_, "SubjectIndex", 1, 1), type table [Student=nullable text, Subject=nullable text, Score=nullable number, SubjectIndex=number]}}
),
Flattened = Table.Combine(AddIndexPerStudentSubject[Data]),

// Step 3: Create unique column name like "Maths", "Maths_2", etc.
AddSubjectIndexSuffix = Table.AddColumn(Flattened, "SubjectKey", each
if [SubjectIndex] = 1 then [Subject] else [Subject] & "_" & Text.From([SubjectIndex])
),

// Step 4: Remove unnecessary columns and prepare for pivot
Cleanup = Table.SelectColumns(AddSubjectIndexSuffix, {"Student", "SubjectKey", "Score"}),

// Step 5: Pivot
Pivoted = Table.Pivot(Cleanup, List.Distinct(Cleanup[SubjectKey]), "SubjectKey", "Score", List.Sum)
in
Pivoted

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!