Power Query Convert Columns to Rows for groups and expand columns dynamically in 2 ways

🔽More Information below🔽

This Power Query challenge uses GroupBy, List functions and a little hack

There's also a trap with expanding the columns if the number of columns may change. I show 2 ways of doing that.

Thanks to everyone for taking part. You can download the challenge file if you want to try this out, and also my solution (and everyone elses) is available here too

I love reading your comments and questions so feel free to post those

Thanks to these folks for taking part, solutions are saved in the same folder as per the link above
Alexander Sorokopyt
Andrew Shepley
Bohdan Duda
Chandeep Chhabra
Christopher Hazel
Daniil Maslyuk
Daryl Lynch
Diana Maria Iordachescu
Dyota
Glyn Willis
Guido
Henriette
Ivan Dumalovski Janjušić
Jan Berny
Kolyu
Leon Dijkers (winner of the first person to submit a solution award 😀 )
Nick Bonner
Nizam
Peter Tholstrup
Philip Treacy
Quadri Atharu
Sham F
Steve Bateman

Cheers

Wyn

[Mister_Bates]

Thanks for replying to my solution email - and loved seeing similar elements in your solution! A few things I learned from your solution:
1) Adding an index to "fix" the sort rather than Table.Buffer - I'm with you on this, PQ can forget the sort.
2) Extracting a list from the grouped tables in the Data column - nice technique
3) Using nQual as a parameter to Text.SplitByDelimiter

I'm loving these challenges - keep them coming!

[ccentermanager]

i was just looking for that solution for over a week!!!! thanks!!! great tutorial

[malejandrahorvath]

Thank you Wyn 😊
Learning so much from you, as always!
The second approach is my favorite one 🤯

[rick_.]

As always I learn something new from you videos, and I thank you!

[khawarmalik]

Second approach is super cool. I knew about the first one having used it on lot of occasions but the second one is amazing. Thanks for sharing his. 👍👍👍👍

[tomr]

Nice! I was on the right path (grouping) and lost track of time. Having said that, I learn by doing - I am going to do both steps tonight! Thanks for sharing!

[wayneedmondson]

Awesome Wyn! Great demo. Thanks for the two examples. Learned a lot! Thumbs up!!

[handlegich]

This is such a massive time saver! I am now interested in the M code!!! Thank you and keep up the good job.

[IvanCortinas_ES]

Thank you!! Congrats for the 10K, and for the new logo. That's an excellent channel!

[shahidabano]

This helped a lot. Saved my time! Thanks.

[jazzista]

Great video. I liked your second approach using the nQuail name and replace the list hardecoded by the M code.

[mirrrvelll]

Great trick! It is amazing and interesting to follow you along!

[davidb]

BRILLIANT STUFF!!! Just what I needed...

[sedarathnadcd]

Thank you Wyan. I learned a lot from your Vedios. 2nd method is more attractive and my favourite. Thank you sharing your valuable knowledge.

[johnhackwood]

Another gem of a video thank you Wyn. If you had just shown the first and left it there that would have been good enough but the 2nd approach, voila!

[matthewwykle]

Just found your challenges. A lot of fun. You can turn off the automatic changed type steps. Data>Get Data> Query Options>Data Load>Type Detection. You can do it for all workbooks (Global) or individual workbooks as needed.

[ivandumalovskijanjusic]

Thanks for the challenge, it was fun and educational, as Power BI should be 🙂

[mirziyodm]

Thanks so much, that was just what I was looking for!

[GrainneDuggan_Excel]

PQ forgot the sort order in my solution to this challenge. I had to sort again at a later step. Definitely still a thing. Thanks for the solution, Wyn

[Bhavik_Khatri]

Wow, very nice tricks. Thank you very much for sharing.