Convert Multiple Column Groups to Rows in Power Query

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
In this video, we'll explore the solution from the last Power Query Challenge!
➜ Download My Solution + Other solutions in the blog comments
goodly.co.in/convert-multiple...
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - [e-mail]
===== CHAPTERS =====
0:00 Intro
0:19 Revisiting the Problem
0:58 Problems with Transpose Operations
1:43 Formative Understanding of the Solution
2:43 Extracting the Names of the Column
4:18 Converting All the columns in 'List' format
6:48 Creating Pair of Two
10:22 Make the Pair Count Dynamic & Get Locations
12:53 Combine Locations and Column Pairs
15:50 Shoutout & My Courses
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!
- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
- Source: goo.gl/fh3rEJ​
Official After The Fall KZbin Channel Below
kzbin.info/door/GQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses

[@MartinKuek]

Knowing what all those functions do is one thing. But your creativity to put them all together into a solution is next level genius.

[@GoodlyChandeep]

Thank you!

[@HachiAdachi]

I KNEW there would be more to it than the Transform-Pivot-Unpivot approach! 😮 I really need to learn this "not breaking the stream" thing... I look forward to your video on that soon! Thank you, Chandeep!! 👍

[@vl21i]

Wow ! What a way to do it without involving transpose anywhere. You have the source of all the DAX formulas and used it in right place at right time

[@BboyDaquack]

I've been searching for 2 days trying to find a solution to this kind of messy raw data with this specific format. I've gotten unbelievably lucky for you to upload this right when I was looking for it! thank you so much!

[@GoodlyChandeep]

Cool! Glad it was helpful

[@AbhayGadiya]

Checkout this alternative solution as well - kzbin.info/www/bejne/iqWldXRpaa2bnJI

[@sztap]

Just found this channel and it's so amezing how much I can learn from it. Thanks for your hard work, please continue sharing your videos.

[@EricaDyson]

Genius indeed! Fantastic. Brilliant! I'm in awe!

[@vineshparekh4228]

I am working on the same problem on my project where I have 9 columns and I need to keep 3 columns and stack the data underneath of 3 columns. This solution will work perfect for me but I need to watch this video for many times to understand better. You're providing solutions to many PQ users! Bravo :)

[@claudiaouellet8499]

This is so visual and well explained; amazing!

[@tracywilliams61]

Amazing, I have this exact problem. You are a great trainer, you explained a difficult solution easy to follow

[@DavidGzirishvili]

Amazing! It becomes an art, an entertainment! Thanks for that and waiting for another release! :-)

[@GoodlyChandeep]

Glad you liked it!

[@sachin.tandon]

This is really good, Goodly! Thank you for sharing!

[@GoodlyChandeep]

Glad you like it !

[@imronnesia5170]

unbelievable. I am new in PQ, just starting to learn it about a week ago by self-thaugt. At the 1st time, i guess this is a course for advance users, but with your excellent explanation, it's easy to understand by everyone. Now i learn many things from your channel everyday. thank you

[@williamarthur4801]

I shall be working through this quite a few times I think, I always seem to learn a new function , if not more, on watching your videos. Thank you. Oh, as someone else has commented "not breaking the stream' , don't understand?

[@SanthoshKumar-cw2kq]

Great explanation with working session for this complex data set. Thanks you !!

[@GoodlyChandeep]

Glad you like it !

[@McKaySavage]

Very helpful solution, and I think fairly elegant. I learned some useful techniques here that apply in lots of other situations too. Thanks!

[@GoodlyChandeep]

Glad it was helpful!

[@MrToypon]

Your way of handling M to flip and twist the data without actually doing the steps is very inspiring and on a whole different level than many other KZbinrs who show step by step code. Keep up the wonderful work of spreading knowledge and understanding! Thank you very much!

[@GoodlyChandeep]

Thanks Tommy.. I should expect to see you around more often:)

[@pbiqueryous]

Exquisite. Simply exquisite. Bravo sir. Thank you for sharing!

[@GoodlyChandeep]

Glad you like it !

[@IlaPatel811]

Incredible! Thanks, I am really getting a better understanding of how M code works.

[@GoodlyChandeep]

Great to hear!

[@martyc5674]

Wonderful Chandeep- you make it look too easy!

[@GoodlyChandeep]

Thanks for watching!

[@larmondoflairallen4705]

Incredible! I have always disliked the transpose/pivot/unpivot approach for anything but the smallest data sets. This is a much better solution.

[@GoodlyChandeep]

Thanks! Glad you like it

[@manuelmontalvo2007]

I like your approach. Keep up the good work!

[@GoodlyChandeep]

Thanks Manuel!

[@imadnb3579]

Brilliantly explained. Cheers

[@GoodlyChandeep]

Glad you liked it!

[@pierre-louisviala3191]

Awesome ... as usual Chandeep !

[@elikent78]

another fantastic video, chandeep. not just teaching us pq. also teaching us to think like programmers.

[@GoodlyChandeep]

Thank you!

[@karimfayazi]

Incredible! Thanks, Thanks for your wonderful video

[@GoodlyChandeep]

Glad you enjoyed it!

[@DeronHuskey]

THAT was cool! Thanks!

[@GoodlyChandeep]

Glad you liked it!

[@lindalai1406]

I also have this same problem. Thank you this creative solution.

[@user-yk9ge7ot7w]

Спасибо, оч круто ) А то я всегда пользовался транспонированием

[@julie_chen]

Unbelievable 👏👏 learned a lot of functions that can be used variably in other aspects too 🎉😊

[@GoodlyChandeep]

Glad you liked it !

[@randolfojolongutierrez5311]

Excelente saludos desde Guatemala!!!

[@GoodlyChandeep]

Thank you!

[@KuldeepSingh-nq1vi]

It was very complicated for me, but you made it so simple.Thanks Bro..

[@GoodlyChandeep]

Glad it was helpful!

[@tonydreisenstock]

Hi I wish I could remember all the M code you use. I just love your solutions and approach to problems. Thanks!

[@GoodlyChandeep]

Glad you like them!

[@avairagade71]

Incredible video it's helpful for my project.. only one challenge in my dataset is date in single column

[@GoodlyChandeep]

Glad it helped!

[@jorstube]

Friend I don't know how many times they have told you; but you are a genius...greetings from Chile

[@GoodlyChandeep]

Thank you for your nice words !

[@abhijeetshetye8785]

Very interesting. I achieved solution with pivot, fill down, unpivot 😅. I short lot of steps I created. thank you for this easy solution.🎉

[@GoodlyChandeep]

Glad it helped!

[@PowerMacro]

I will try with my data, hopefully it will be automate my work. THANKS A MILLION PAAJI ❤️

[@GoodlyChandeep]

Always welcome

[@kennethstephani692]

Great video!!

[@GoodlyChandeep]

Thank you !

[@abhiadfree6331]

You are absolutely crazy genius

[@GoodlyChandeep]

Thanks !

[@julie_chen]

Unbelievably super dynamic...

[@GoodlyChandeep]

Thanks!

[@shreedharan.moorthy]

You are incredible

[@navedsaiyed9881]

Thank you for creating this video Exact solution to what i was looking 4.........Cheerzzz

[@GoodlyChandeep]

You're very welcome!

[@dieudonnepare9295]

very Great !!!

[@GoodlyChandeep]

Thank you!

[@iankr]

Wow! This is so powerful. As Martin Kuek says, there's understanding the separate functions, but you also need to know how to combine them in the stepwise-driven process that is Power Query. So far, my PQ experience is with each step referring to the previous one. But here, you're referring back to earlier steps. I came across this video via a Google search, and I think I'll be able to adapt your techniques into what I need to do. Many thanks!

[@nies_diy986]

Super amazing 😍

[@damionc]

You are the boss!!!

[@GoodlyChandeep]

Thank You !

[@smartwork4768]

Awesome trick.

[@GoodlyChandeep]

Thanks!

[@Dev_Bartwal]

Fantastic Paji You are rock

[@GoodlyChandeep]

Thanks !

[@ShubhamSharma-ls6hj]

Hi bro, thanks for your effort today I learned one more new thing. Do you have any solution to automatically track ranges in various excel sheet in PQ. As I need to append them and every time i have to convert data in table form.

[@DeeptiDubey1380]

You have a indepth knowledge of DAX and it's become tricky for people like us who are still learning. 😁😁😁😁

[@Milhouse77BS]

This reminds me of functional programming in a Lisp, like Clojure.

[@zahoorsarbandi2982]

Superb!!!!

[@GoodlyChandeep]

Thank you!

[@s1ngularityxd64]

amazing solution🙂

[@GoodlyChandeep]

Thanks!

[@puthdarakim1175]

very good

[@lopher70]

This is dam good!!!

[@GoodlyChandeep]

Glad you like it !

[@SamehRSameh]

Greatest 🎉🎉

[@GoodlyChandeep]

Thank you !

[@theexcelproject6973]

That's a lot of helpp, especially to someone like who is just starting with M.

[@GoodlyChandeep]

Glad it was helpful !

[@theexcelproject6973]

@@GoodlyChandeep Hey! It is. A lot of thanks from the Philippines. I just started a Reporting job so this is really helpful.

[@santoshhmt]

Hi, these steps would be considered as query folded?

[@FRANKWHITE1996]

Magic 🎉❤

[@GoodlyChandeep]

Thank You !

[@AbhayGadiya]

Please check the alternative solution of using Pivot and unpivot method without breaking streaming of power query

[@AbhayGadiya]

Link to video - kzbin.info/www/bejne/iqWldXRpaa2bnJI

[@txreal2]

How do keep only columns I need/remove other columns based on a List? How do I create that List in Power Query or import that List? Thanks

[@Chris-tj4es]

Let's say you have an additional column at the begining of the table: The name of the column would be CompanyName and in this column from row 2 to 7 you would have the name of company. How would add this colmun at the end of your model? thanks

[@modernboutique1389]

Nice

[@GoodlyChandeep]

Thanks!

[@ItsNotAboutTheCell]

Now that's some good M :)

[@GoodlyChandeep]

Thank You !

[@victor_wang_1]

This solution, namely using List.Split, assumes a consistent number of sub-headers for each city. If any city had a different number of sub-headers than the rest, this would fail. I considered using List.Split, but I thought it violated the conditions of the challenge as its not truly dynamic. Garucia had an excellent solution that was fully dynamic.

[@adlaalnajmi9162]

Which channel you referring to?

[@GoodlyChandeep]

Yes. I agree. Her code is fantastic!

[@GoodlyChandeep]

See the blog comments - goodly.co.in/convert-multiple-column-groups-to-rows-power-query/

[@adlaalnajmi9162]

I cold not find Garcinia channel. Can you send me the lik, pls

[@victor_wang_1]

@@adlaalnajmi9162 See the blog comments as Goodly linked in this thread

[@ssomtom]

Fantastic. But what's happening how can your method improves if only Mumbai has a plus sub-column, so not all cities have same amount

[@anneerrudhmmahepate967]

Hello Chandeep, I am stuck at 1:49 . Can you please explain the line Source{0}[Data]. When I am loading the data, power query is converting it into excel and then loading.

[@ExcelWithChris]

Absolute genius! Any courses for beginners?

[@GoodlyChandeep]

See this - goodly.co.in/learn-power-query/

[@ExcelWithChris]

@@GoodlyChandeep does this include M? I have a good understanding and use of PQ, need to learn M from scratch and the way you explain it is brilliant.

[@GoodlyChandeep]

@@ExcelWithChris I am currently working on a course on M Language.. You'll hear the announcement on KZbin soon :)

[@akkintouch]

Everything is correct but I have 1 more column which I want to accumulate into each column group is that possible? Someone please help me

[@DCarW]

I'm trying to do this with some longitudinal data. The initial column has meaning and I want to retain it and merge it with each set of 2 values. SourceDataHeaders: SomeIDThatHasMeaning,Name1,Age1,Name2,Age2,Name3,Age3,.....,Name40,Age40 EndDataHeaders: SomeIDThatHasMeaning,Name,Age I was hoping I could remerge the list (one list for all the IDs and another with my split sets of 2) but haven't figured that out yet.

[@kameshsharma5164]

Hello Sir Please make a power bi complete tutorial from beginner to advance level in Hindi

[@walterstevens8676]

Basic question, but when you start by extracting the columns names, you have an M Query line Sheet1_Sheet{1}. What is Sheet1_Sheet, because I get a message saying that Sheet1_Sheet isn't recognised ? I've tried the table name_Sheet and that didn't work

[@AAAExcel]

Fantastic as usual, one comment, if we add sub column to only one location the query won't work precisely. i did a solution using a combination of transpose group by and pivot (I know it is not as clean as yours) but it caters for this probability. at the end, i can't thank you enough for the sharing your valuable ideas

[@eslamfahmy87]

hi, i think that solution is very hard! why you do not go with transpose and then merge the row headers and after that use unpivot other columns.

[@PrabhatKumar-tn3be]

your videos are really helping me a lot in M. I am stuck in power query if you could please help me. I am not able to write dax or command excel line in M if you could please help me =VALUE(MID([DevData],FIND("CT",[DevData])+3,FIND(";",MID([DevData],FIND("CT",[DevData]),LEN([DevData])))-4))

[@akkintouch]

What if I have another column How can I bring them into the column pairs Example I have a column named index It contains numbers 1-6 So using your solution i will just take everything other than what it is colA Then I want to make sure col A is added into the col pairs of name and age How do I put that?

[@rajrawat287]

You look like a magician who does belive in Impress others rather than teaching

[@GoodlyChandeep]

Thanks Raj :)

[@sachin.tandon]

This can also be solved using the native Excel array formulae...

[@dineshnegi75]

Dear Charan, Need a solution for extracting the data from a table which has values in the below columns: Column_A, Column_B, Column_C, Column_X, Value X, Column_X, Value_Y, Column_X, Value_Z In such as way that it looks like Column_A, Column_B, Column_C, Column_X, Value_X Column_A, Column_B, Column_C, Column_X, Value_Y Column_A, Column_B, Column_C, Column_X, Value_Z Which means that here the Column_X has been repeated 3 time (in actual report this is repeated 30 times) and after each Column_X, value of that is placed in the next column against the same (Value_X, Value_Y, Value_Z ...... again in 30 columns). Look forward to a solution.

[@milux616]

🤩

[@GoodlyChandeep]

Thanks !

[@KawanExcel]

this guy is god of M

[@GoodlyChandeep]

Glad you think so!

[@bagnon]

I don't see the name "Sheet1_Sheet" on your list of applied steps, so I don't understand how you are able to refer to it.

[@GoodlyChandeep]

the navigation step is called Sheet1_Sheet

[@bagnon]

@@GoodlyChandeep Thanks, I guess I missed how you give a step a custom name without changing the displayed name.

[@GoodlyChandeep]

@@bagnon Well you cannot. That unique anomaly is only limited to the Navigation Step ;)

[@richgeraldgarrido9944]

Why is the First step Sheet_Sheet1 instead of Navigation?

[@GoodlyChandeep]

Haha.. Navigation is reserved name. You cannot use it. Just hop over to the advanced editor to see the actual name of the navigation step.

[@mrunalbhongade8268]

Please teach .lam 14 and would like to be an amazing DJ just like marshmallow

[@kasmirasmarzo]

Great video, but not all of us are fellas

[@GoodlyChandeep]

Friends feels too clichéd. I am treating "fellas" as gender agnostic 😜

[@chengwang411]

why do i bother? it's out of my league 🤣🤣