Unstack a Million Uneven Rows to Columns in Power Query

In this video, we will discuss a smart & efficient way to unstack a large number of uneven rows in separate columns using UI & M Code in Power Query

===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

===== LINKS 🔗 =====

===== CONTACT 🌐 =====

===== 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!

[NeilAdamson-mc]

This is 100% EXACTLY what I was looking for, down to the blank rows as separators! Astounding as always.

[nboisen]

Your automating column renaming process is fantastic and simple and useful in many scenarios. Pity it gets hidden at the end of this video as it deserves a video on its own.

[Excelambda]

Great video!!
lambda alternative: VUNSTACK(v) Vector Unstack
=LAMBDA(v, LET(a, (v="")*SEQUENCE(ROWS(v)), b, FILTER(a, a), c, VSTACK(0, DROP(b, -1)), d, b-c-1, e, SEQUENCE(ROWS(b))^0+SEQUENCE(, MAX(d))-1, IF(e<=d, INDEX(v, e+c), "")))

=VUNSTACK(LargeData[Data])

[Ypurenete]

Great!! I have used a mix of your two unstack videos to solve a big problem here. Thanks a lot!!

[markrobinson]

the challenge with uneven columns in Real life is slightly different, you might have Name and Number as the only two fields available on a record. While your solution is pretty amazing, but if you can address this case it will be real help!!!

[alexandrubadiu]

Love the clear way you explain power query. Great teaching method backed up by very intelligent animations! Great job!

[amyhunter]

Thank you for sharing your knowledge so generously. Super helpful for me!

[masgitz]

Man, this is gold. Chandeep, I have managed to do most of these steps to an irregularly formatted PDF. I have the entire page of each PDF in a single row. Now I need to extract from each the date of an event, the event number which has a specific alphanumeric format, and also extract when there are multiple event numbers same format on same date. I'm thinking using the split functions, but getting stuck here - maybe you can consider this for future videos😅

[ArthurCapers]

Thank you for the video - I have been able to adapt it to my particular problem - like your example - over 700K lines and has largely cleaned the data but most importantly placed the appropriate data on a single line.

[sasdas]

Thank you Chandeep, they way you explain is amazing! one question how can we do it when we have two columns instead of one. The second column has repeated Headers.

[nishantkumar]

Awesome, Thank you so much.
Just an addition : Instead of creating excel table for column names we can create nested list within Power query and use it to rename columns. It will save us from an extra connection of table.
Limitation: It won't be as flexible as Excel table for new users but advance users can do it pretty easily.

[douggrant]

This is another great video, though, but how would you deal with this little change in scenario? The same data set, but where the employees don’t have all 5 pieces of data, you don’t know that the ones they have are always the first 3, or 4 etc. what I mean is that some incomplete records might be (Name, City and Age) and others might be (Name, Age, Phone). Do you e what I mean? I think this is a more likely scenario. All you can be sure of is that the data will always be supplied with the fields in a specific order, if they exist. I’d love to see this. Thanks
**I see this was answered previously - apologies **

[sreennivasan]

Such a wonderful video....But I have a if I do not have an identifier (space in between each item) as in your example...Could you help me out

[AanandP]

Hi Chandeep, what if the columns missed in the records are not at the end, but in between, For instance if age is missing in one record with all other values available.

[RamKumar-vbet]

Excellent view. Thanks for sharing. A query: If I have multiple column unstacking problem (example, data is in multiple rows with each row having a different combination of id, course, grade, university). Is it possible to unstack it and have data reorganized with columns headings id, course, grade, and university)? Thanks,

[maheshoza]

Very good technique short and sweet... If multiple columns has uneven records can we apply same logic. Please make clips multiple uneven row and more than 10 column having uneven data

[beepboopbeepboop]

This is perfect timing! I am working on a dirty data set with this exact problem right now!

Your videos are the best Chandeep!

[kkhalidabed]

Amazing Goodly ! May a question how do the UNICODE function work in Power query ?

[warrenanderson]

Great video...Icwas working on this issue I had with a client's PDF Report that I impoerted into Power Query. Had to do a manual transfer. Wish this video was created sooner. Power Query is a BIG Game Changer for Data Analysis . I have gotten many jobs just from my limited (but growing) knowledge. 😂

[latrisadavis]

Good video. I just wish that it did not assume that everyone understood more simple steps like writing what if commands etc. Because I got all the way to the index column but after that everything got lost. It just assumes that everyone understands every step and something can be skipped.