SOLVED

Transform my data from multiple lines to one line per student

Copper Contributor
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
100123457. John Doe. PHM 123 A
100123457 John Doe PHM 124 C

Transformed into:
ID. FN. LN. PHM 123. PHM 124
100123456 John Smith A. B
100123457. John Doe. A. C
9 Replies

@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.

My data is already in an excel table. Is there a specific video you can direct me to? I’ve been searching and I haven’t found the particular video.
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.

@Drpstone Perhaps the attached file holds a solution that works for your. One with Power Pivot and one with Power Query. I didn't pay much attention to making the column headers nice, but that's something easily adjusted. Neither of them is very complicated.

 

@Drpstone This an introduction to PQ and Power Pivot https://youtu.be/ohGFPF12Qwc

 

@Riny_van_Eekelen 

 

Yes...that's it.  How did you do it?  I'm a newbie and figuring out most things by trial and error.  Can you send the steps? Or a link to a youtube video with the steps explained.

 

Thanks,

Phyllis

 

 

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.
best response confirmed by allyreckerman (Microsoft)
Solution

@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. 

Thanks!!! Looks like I need to view a few basic videos to get me on first base. This is helpful!

Phyllis
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@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. 

View solution in original post