# Thread: Transpose uneven data sets

1. ## Transpose uneven data sets

I have seen solutions to this sort of problem solved using VBA, but know so little about programming I could not figure out how to apply it to my data.
I have the data below but have also attached the spreadsheet.guru.xlsx

Here is the input:
 Student Course Grade Student 1 ZZZ 129 B+ Student 1 ZZZ 130 B Student 1 ZZZ 131 B+ Student 1 ZZZ 132 A- Student 2 ZZZ 127 B+ Student 2 ZZZ 128 B+ Student 2 ZZZ 129 C Student 2 ZZZ 130 B Student 2 ZZZ 131 C+ Student 2 ZZZ 132 B Student 3 ZZZ 129 C Student 3 ZZZ 130 B Student 3 ZZZ 131 C- Student 3 ZZZ 132 B Student 4 ZZZ 125 B+ Student 4 ZZZ 126 B+ Student 4 ZZZ 127 C Student 4 ZZZ 128 B Student 4 ZZZ 129 C+ Student 4 ZZZ 130 B Student 4 ZZZ 131 C Student 4 ZZZ 132 B

Here is the output I would like. I have hundreds of rows of data and would rather not transpose by hand for each student.
 ZZZ 125 ZZZ 126 ZZZ 127 ZZZ 128 ZZZ 129 ZZZ 130 ZZZ 131 ZZZ 132 Student 1 B+ B B+ A- Student 2 B+ B+ C B C+ B Student 3 C B C- B Student 4 B+ B+ C B C+ B C B

Any help would be appreciated.

2. Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
1
Student ZZZ 125 ZZZ 126 ZZZ 127 ZZZ 128 ZZZ 129 ZZZ 130 ZZZ 131 ZZZ 132
2
Student 1 B+ B B+ A-
3
Student 2 B+ B+ C B C+ B
4
Student 3 C B C- B
5
Student 4 B+ B+ C B C+ B C B
 Sheet: Table1

Power Query Mcode
Code:
```let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Course]), "Course", "Grade"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Student", "ZZZ 125", "ZZZ 126", "ZZZ 127", "ZZZ 128", "ZZZ 129", "ZZZ 130", "ZZZ 131", "ZZZ 132"})
in
#"Reordered Columns"```

3. Thank you. This is definitely what I am looking for. But trying to get this into my excel file has cost me more than an hour now. I have never used Power Queries before and looking up tutorials has got me close, but not quite there. I did finally get it to run, but the output was not quite right. I am using Excel 2019.
I open the file. In the data tab, under get and transform data I select from Table/Range and select my data. I open the advanced editor and this is the code that is there
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", type text}, {"Course no.", Int64.Type}, {"Course", type text}, {"ID", Int64.Type}, {"Grade", type text}, {"Student Count", Int64.Type}})
in
#"Changed Type"

I replace that with the code given above and used my real data. What comes out is full pivot table with empty cells where where is no letter grade.
Any help would be appreciated.

4. You've got more columns in your source data than you indicated in your first post.
The following is a guess, but attach a realistic workbook to get a solution more likely to work.
Code:
```let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"Student", "Course", "Grade"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Course]), "Course", "Grade")
in
#"Pivoted Column"```

5. Amazing. That solved the problem. Thanks to the both of you for your help.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•