# times Student taking same course & number of times students take multiple courses in the same term

[Vtomsons]

Hi,

I have a large single enrollment dataset that I'd like to show how many times the same course (Course name) are repeated by the same student (Person ID). Example of the data set is below. I'm struggling on the best way to structure this but am leaning towards by course and having a column each for how many times the student enrolled in the course (i.e. once, twice, three, etc...). Thanks for any advice on how to approach this.

Course Name	One time	Two times	Three times
ADM*1165	1	1	0
ADM*1213	1	1	0
ADM*4326	2	1	0
SOCI*3634	4	1	1

Data Set:

Course Subject	Course Number	Course Name	Registration Term	Person ID
ADM	1165	ADM*1165	2019/SM	3558252
ADM	1165	ADM*1165	2020/SM	3333333
ADM	1165	ADM*1165	2020/SM	3558252
ADM	1213	ADM*1213	2019/SM	3431187
ADM	1213	ADM*1213	2019/SM	3333335
ADM	1213	ADM*1213	2020/SM	3431187
ADM	4326	ADM*4326	2022/FA	3558252
ADM	4326	ADM*4326	2022/FA	3333333
ADM	4326	ADM*4326	2022/FA	3333334
ADM	4326	ADM*4326	2022/WI	3558252
SOCI	3634	SOCI*3634	2020/FA	3333336
SOCI	3634	SOCI*3634	2021/FA	3333336
SOCI	3634	SOCI*3634	2022/FA	3558252
SOCI	3634	SOCI*3634	2022/FA	3333336
SOCI	3634	SOCI*3634	2022/WI	3558252
SOCI	3634	SOCI*3634	2022/WI	3333337
SOCI	3634	SOCI*3634	2022/WI	3333338
SOCI	3634	SOCI*3634	2022/WI	3333339
SOCI	3634	SOCI*3634	2022/WI	3333340

[ronrsnfld]

Group by Course Name and Person ID with Count aggregation. Then Pivot on the Count field with Count aggregation:

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Course Subject", type text}, {"Course Number", Int64.Type}, {"Course Name", type text}, 
        {"Registration Term", type text}, {"Person ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Course Name", "Person ID"}, {
        {"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", 
        {{"Count", type text}}, "en-US"), 
        List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", 
            {{"Count", type text}}, "en-US")[Count]), "Count", "Person ID", List.NonNullCount),
    
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Course Name", "1", "2", "3"})
in
    #"Reordered Columns"