Forum Discussion
Efficient Way to Pivot Table with Duplicate Subjects per Student in Power Query
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"
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
PivotedDid I answer your question? Mark my post as a solution! Appreciate your Kudos !!
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"
6 Replies
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
PivotedDid I answer your question? Mark my post as a solution! Appreciate your Kudos !!
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"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.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.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.
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
