CODEWITHSUNDEEP
excelpowerqueryvba
JP_CPT
JP_CPT

Reputation: 13

Transpose to multiple columns wrap n

I need to transpose/pivot student numbers AND Names from rows into multiple columns, but is limited to 15 columns, so I need to start a new row should there be more than 15. This needs to be for each center, subject and type. I’d like to either transfer to a new sheet or strip out the extra rows. I’ve not been able to successfully STEP both columns into new columns repeatedly. There are in the region of 500,000 rows. Please help I’m stuck!

For example:

Center  Course  Type    Student_Num Student_Name
1001    Science 1   1   John
1001    Science 1   2   Linda
1001    Science 1   3   Pete
1001    Science 2   1   Susan
1001    Science 2   2   Gary
1001    English 1   1   Bob
1001    English 1   2   Kate
1002    Science 1   1   Alice
1002    Science 1   2   Rick
1002    Science 1   3   Pat
1002    Science 1   4   Shanon
1002    Science 1   5   Louis
1002    English 1   1   Cathleen

Transformed:

Center  Course  Type    Student1_Num    Student1_Name   Student2_Num    Student2_Name
1001    Science 1   1   John    2   Linda
1001    Science 1   3   Pete        
1001    Science 2   1   Susan   2   Gary
1001    English 1   1   Bob 2   Kate
1002    Science 1   1   Alice   2   Rick
1002    Science 1   3   Pat 4   Sharon
1002    Science 1   5   Louis       
1002    English 1   1   Cathleen

Hope this is not duplicated, but I’m not able to find anything that can give me the correct results Thanks

Upvotes: 0

Views: 161

Answers (1)

MarcelBeug
MarcelBeug

Reputation: 2967

A solution with a parameter, a list and a query.

It performs quite well:

I tested in Excel with 100,000 records and it took about 15 seconds;

500,000 records in about 2:15 minutes.

It is dynamic with regard to the number of students per row.

Parameter MaxStudentsPerRow:

2 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]

List ExpandList, resulting in nested lists with:

"0", "Student1_Num", "Student1_Name"

"1", "Student2_Num", "Student2_Name"

etc.

let
    Source = {1..MaxStudentsPerRow},
    ToText = List.Transform(Source, each {Text.From(_-1), "Student"&Text.From(_)}),
    AddedNumAndName = List.Transform(ToText,each {_{0},_{1}&"_Num",_{1}&"_Name"})
in
    AddedNumAndName

Query: basically an Index is added for each key-combination (via Table.Group), some indices with modulo and integer-divide calculations are added as preparation for pivoting, num and name are combined in a record, the table is pivoted (with advanced option "Don't Aggregate"), and the nested records (with num and name) are expanded using List.Accumulate and the data in ExpandList.

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"Center", "Course", "Type"}, {{"AllData", each Table.AddIndexColumn(_,"Index",0,1)}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Student_Num", "Student_Name", "Index"}, {"Student_Num", "Student_Name", "Index"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded AllData", "Index", "Index - Copy"),
    #"Calculated Modulo" = Table.TransformColumns(#"Duplicated Column", {{"Index", each Number.Mod(_, MaxStudentsPerRow), type number}}),
    #"Integer-Divided Column" = Table.TransformColumns(#"Calculated Modulo", {{"Index - Copy", each Number.IntegerDivide(_, MaxStudentsPerRow), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Integer-Divided Column", "NumAndName", each Record.FromList({[Student_Num],[Student_Name]},{"Num","Name"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Student_Num", "Student_Name"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Index", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Index]), "Index", "NumAndName"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index - Copy"}),
    Expanded = List.Accumulate(ExpandList,#"Removed Columns1",(t,e) => Table.ExpandRecordColumn(t,e{0},{"Num","Name"},{e{1},e{2}}))
in
    Expanded

Upvotes: 1

Related Questions