Forum Discussion
Solved
Transform my data from multiple lines to one line per student
I need to transform my data multiple grades per student to one line per student with multiple columns for grades. For example: 100123456. John Smith PHM 123 A 100123456 John Smith PHM 124 B 10...
Drpstone The Power Query (PQ) solution requires only two steps. Connect to the (structured) table.Unpivot without aggregation. Perhaps a good idea to learn more about the basics from the attached link.
https://exceloffthegrid.com/power-query-introduction/
I can't really explain it better myself. Then you can follow the "applied steps" in the file that I sent earlier.
Drpstone Power Query can transform this into the format you need. But first you need to convert your data to an Excel table. Check out Mike Girvin 'Excel is Fun' on You Tube for tutorials on how to do this.
Also, I can create a table using power query in multiple steps. I’m looking for an easier process. And a way to create a formula to count specific highlight specific grade combinations.
- A quick method I use if I'm in a rush is it send the data to pivot table, format how I like. Then copy/paste to a new worksheet. I guess it depends how frequently you are going to need to do this. If you're going to do this regularly PQ is the way to go. But it's worth thinking about how PQ gets and transforms your data first. PQ can get your files from folders, refresh and you're done. However you need to set this process up first to tell PQ where your file is, what transformations you want to make. Then you can update the file each week/month, refresh and it will update your grade combinations.