Forum Discussion
Transform my data from multiple lines to one line per student
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
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.
9 Replies
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.
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
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.
- 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.
Drpstone This an introduction to PQ and Power Pivot https://youtu.be/ohGFPF12Qwc
