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

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 !!

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"