Unstack a Million Uneven Rows to Columns in Power Query

Amazing 👏

fantastic

super awesome indeed! you're a nerd man...

Thanks sir,
I want to transpose data like this format
Heading
A
B
C
Heading1
A
B
C
D
Heading2
A
B
C
D
Heading3
A
B
C
D
E

Now need to data below format, that is possible sir, if yes could please help
Here is not black row, this an example for data. Actually I have 1 lac row and I want to transpose in this format
Heading A B C
Heading1 A B C D
Heading2 A B C D
Heading3 A B C D E

Mera to jeewan safal ho gya ye video dekh ke apka bahut bahut abhar

Hello Goodly. I have been watching your videos and they are really helpful. I have a situation that is the inverse of what you did in this video. I have a table with ID, name, and email. What happens is that some rows have 1 email, others 2 and some have 10 emails. I want to generate a list or a table with the ID and individual emails on on top of the other so that I can submit it to a email higiene pass to eliminate hard bounces and invalid emails. Would be really cool if you could help me out. I am faily new to Power Query and sometimes it gets a bit overloaded and freezes me up. Best regards

I have 1 m record on which I want to apply group by to get max value of each category and then same value should reflect against each category in separate custom column.....I tried it but it will take almost 2 hours and eventually system hanged....any suggestions pls

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

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.

i Have table Employee code Company Name Tenure - DOJ Tenure - LWD
1 a 01-Jan-23 01-Apr-23
1 b 15-Jun-22 13-Sep-22
2 aa 10-Jan-23 10-Apr-23
2 bb 24-Jun-22 22-Sep-22
2 cc 16-Mar-22 14-Jun-22
3 aaa 20-Jan-23 20-Apr-23
3 bbb 04-Jul-22 02-Oct-22
3 ccc 26-Mar-22 24-Jun-22
3 ddd 27-Oct-21 25-Jan-22
i want data like this : Employee code Company Name Tenure - DOJ Tenure - LWD Company Name Tenure - DOJ Tenure - LWD Company Name Tenure - DOJ Tenure - LWD Company Name Tenure - DOJ Tenure - LWD
1 a 01-Jan-23 01-Apr-23 b 15-Jun-22 13-Sep-22
2 aa 10-Jan-23 10-Apr-23 bb 24-Jun-22 22-Sep-22 cc 16-Mar-22 14-Jun-22
3 aaa 20-Jan-23 20-Apr-23 bbb 04-Jul-22 02-Oct-22 ccc 26-Mar-22 24-Jun-22 ddd 27-Oct-21 25-Jan-22
can you able to help

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,

Great work,

Please, can you build your qurey with us step by step rather than showing the applied steps

Hi could you share the M Codes so that we dont have to type in manually

Hi Goodly
I have a column in which there is employee id and on a different row there is the bank name.I want to these 2 information on 2 separate columns.please help
Thks

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.

How the heck this guy get this Logic ???.... Its Just awesome. Thanks Chandeep

Really good insight, however it didn’t work for me 😢 Had to trim the data manually 😢 Thankfully there were only 1,300 entries

Chandeep - the Power Query GOAT!

Hi Chandeep - what if the data isn’t conveniently split up with null rows? I have data that always has at least 4 rows, but sometimes 5 and sometimes 6 rows, and it’s all stuck together.

Is there a solution for unpivoting as well?

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

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

Super helpful as always Chandeep! Thank you for sharing!

So love your solutions and approach. Any special prices on your power query course.

Great examples of how to transform lists into tables and vice versa! Thank you 😊.

Superb..Hats off to you

Love you!

i saw this trick somewhere - you have data & record # - you group the by the record # and SUM the data value - this causes an error - in the group step manually change the List.Sum function to Text.Combine set your delimiter - split the value and you are done

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

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.

What if you got more than 3 nulls (2 intersect by a text row, and 1 before the next set)?
The Filled up step did not come out right.
Any ideas is appreciated. Thanks.

Can we instead do this Without applying M language , just using the User Interface 😢? I am finding hard to learn M.

Sir, when I try to load a data into power query with more than 5000 rows it only loads 33 rows. What may be the reason??

Thanks for vedio

Thanks dear, please help me how to data export in multiple workbook from data in Power query

Hi @goodly, how come the columns get aligned so nicely? I was not expecting this. Seeing some interesting replies too that need to be studied.

❤

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.

Buen video!!! Pero que pasaría en el caso de que en ciertos grupos de datos faltará el número de teléfono, pero en otros faltará la edad o otro dato?

very nice, zabar10...

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

Your videos are the best Chandeep!

I'm getting a Network Error when I try to download the file whether or not I subscribe.
Since I couldn't get the file, I entered data manually, but the video didn't show that there was a fifth field, so when I tried to do the Rename Columns I got an error because there was no fifth column. Luckily I remembered that Table.RenameColumns has a third option which defines how to handle missing fields. They are:
MissingField.Error (default if no third option present), MissingField.Ignore (which left out the 5th column for me), and MissingField.UseNull (which creates the column and fills it with nulls).

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

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 *

Hi Chandeep... Great video as always..

Can you create a video on connecting power bi to service now and Jira.. Adding to it.. There is a complex procedure to set up a data refresh in workspace... If you can make a video on that... It will help many.. Trust me

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.

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

Incredible Goodly !

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])

Perfect. Thank you for sharing.