Thank you! This is exactly what I was looking for.

@Computergaga

2 ай бұрын

Glad it was helpful!

Thank you so much for sharing this functionality, it saved me a tremendous amount of time :)

@Computergaga

5 жыл бұрын

You're very welcome.

This just saved me and my team about 30 hours of manual work. What a fantastic tip&trick! This is a must learn for analysts that receive text data from ancillary feeds! Tank you so much!

@Computergaga

4 жыл бұрын

You're very welcome. These comments is why I do it. So happy to help Greg.

Thank you for the lesson, it indeed helps me finish my administrative task quickly.

@Computergaga

5 жыл бұрын

Great to hear. Thank you ardhitya.

Excellent! Thanks for sharing this lesson. It helps to finish my work at a faster rate.

@Computergaga

3 жыл бұрын

Awesome! You're welcome Srikanta.

Thank you so much!!! Such a life saver for my academic research !!!!

@Computergaga

4 жыл бұрын

Great to hear that I could help Yueliang. It is my pleasure.

thanks you are a excel guru it worked perfectly

@Computergaga

2 жыл бұрын

No worries. Thank you, Stan.

Awesome. Simple and effective. Thank you so much!!!

@Computergaga

4 жыл бұрын

You're welcome Basaveswara. Thank you.

Thank you so much for this simple yet helpful tutorial, much love from the Philippines :)

@Computergaga

4 жыл бұрын

You're welcome. Thank you.

Man you saved me from long pointless work with this PowerQuery tip, thanks mate!

@Computergaga

3 жыл бұрын

No problem 👍

Thank so much you just solved my problem today! WFH here in Philippines! God bless you!

@Computergaga

4 жыл бұрын

Glad it helped. Take care Jaskie.

Thanks for this great functionality.. will save me tons of time

@Computergaga

4 жыл бұрын

Excellent! You're very welcome.

I think this might be rare video which is very helpful to know some basic fundamentals about what Power query is andd how to use it. Thanks

@Computergaga

4 жыл бұрын

Thank you Tarun.

Thanks Alan for this video, appreciate your dedicated work in sharing your training videos Best Regards Mohideen

@Computergaga

6 жыл бұрын

You're welcome Mohideen, thank you.

Excellent mate! You helped me a lot. Cheers from Brazil.

@Computergaga

2 жыл бұрын

No worries. Thank you, Rodrigo.

Thanks that worked perfectly!

@Computergaga

4 жыл бұрын

Excellent! Happy to help Stephanie.

Thank you for solving my problem on splitting !

@Computergaga

3 жыл бұрын

No worries! My pleasure Nishantha.

Thank You! Was very useful!

@Computergaga

3 жыл бұрын

Awesome! 😊

Thanks a lot! You are amazing, bro!! Blessings!!

@Computergaga

2 жыл бұрын

Thanks 😊 Blessings to you also Miguel.

Thank you so much for sharing

@Computergaga

6 жыл бұрын

You're welcome Syed.

Thanks you so much. Exactly what I need.

@Computergaga

2 жыл бұрын

Great to hear. Happy to help.

big thanks to you, sir. You just saved my life.

@Computergaga

3 жыл бұрын

My pleasure, Nguyễn 👍

Very Useful!

@Computergaga

4 жыл бұрын

Thank you.

Is there a way to get a single long row of data into multiple rows of equal colums. For eg. I have 1 row with columns A-ABC but I need it as a table of rows with data only from A-Q columns. Please comment the link if you already have a video on this. Thanks!

It’s very useful!! Thankiew

@Computergaga

2 жыл бұрын

You're welcome 😊

You just saved me hours of manual work. Haha. Thanks a lot.

@Computergaga

5 ай бұрын

My pleasure 👍

Hello, How can I do a reverse of this? I have multiple values in a cell in power query, separated by comma and I would like to display it with a carriage return (alt+enter) when I load it to table in excel. Is this possible?

Hi, my table has multiple columns that need to be split. But this function only works to split one column only. Is there a way to split multiple columns?

@dimasyogapratama3930

Жыл бұрын

Hi did you manage to solve your case??please let me know, i have same problem

thank you very much!

@Computergaga

4 жыл бұрын

You're welcome Manish.

THANK YOU!

@Computergaga

3 жыл бұрын

You're very welcome 👍

Smart thank you

@Computergaga

6 жыл бұрын

You're welcome Ismail, thanks.

Hi, thanks for the great video! Do you know if it's possible to do it in Google Sheets as well?

@Computergaga

4 жыл бұрын

Thank you Shirley. Sorry, but I do not Google Sheets well enough.

Hi Computergaga! Could you please tell me what's in the bracket?

thanks for sharing

@Computergaga

6 жыл бұрын

You're welcome Haider.

Thanks a lot for the video..Wonderful VIdeo

@Computergaga

3 жыл бұрын

Thank you very much.

Thank you sooo much

@Computergaga

3 жыл бұрын

Welcome Roger 😊

thanks for your video, very helpful. Do you know how if I want to split them after 2nd LF?

@Computergaga

3 жыл бұрын

Thank you, Ferry. You could maybe split by the first occurrence and then do it on the first occurrence again.

Interesting!

@Computergaga

4 жыл бұрын

Thank you Steven.

This is great! Thank you so much. Do you think it would be possible to split multiple columns into rows? Suppose instead of just one result column, you have resultA and resultB, both with the same structure, i.e. same number of line breaks.

@Computergaga

3 жыл бұрын

Thank you. Multiple line breaks, yes absolutely. If you did want columns into rows, you could use Unpivot Columns - kzread.info/dash/bejne/ZJ2MyKutlbLIibA.html

@jiewenluo5545

2 жыл бұрын

I want to ask the exact same question!!!!

Thanks a lot

@Computergaga

Ай бұрын

Most welcome

Thanks for this video. In this example, how can I tell PQ to provide me with a table that contains the office name, and only the last number In the result column, such as (London 20). Thanks.

@Computergaga

6 жыл бұрын

You're welcome Lotfy. We could perform the steps in the video. And then insert an Index column from 1 on the Add Column tab. Then Add a Custom Column and insert the formula - =Number.Mod([Index],4). This will result in 0 for the last result for each office. We can then filter that column to show only those with 0 in the Mod column. Then remove the Index and Mod columns and load to the table. Hope that makes sense.

Thanks Alan. You turned a mountain into a molehill!

@Computergaga

4 жыл бұрын

Great to hear Rajan 👍

Hi. could you please tell me how can I split if I have two or more colums in which split is required

@ravikpatel

3 жыл бұрын

did you figure this out?

@bartoszbieniek3042

3 жыл бұрын

for future please try watching this video: kzread.info/dash/bejne/fIGD0NRsdLLXqc4.html Also remember to use "#(lf)" instead of ";" as you want to split after line break!

This method works if you have only one column. But not for more than one column to split at once. The option "Split Column" is grayed out.

Hi first thanks for this video. I have a csv file where there is 2 columns that is comma separated (size and stock) and when i split fx the size everything is okay like your video - but when i split stock the same way everything changes so where fx size was S, M, L it is now S,S,S,S,M,M,M and so on. So the second separation splits the first separation again.... So how can i split again without the first separation splits again? Hope it makes sense :-). Thank you

@Computergaga

5 жыл бұрын

It should split the columns that are selected so I think you must have accidentally selected both. You could modify this by deleting a step in the applied steps on the right.

@erp73

5 жыл бұрын

@@Computergaga Hi yes that is what I thought at first, but I have tried it a few times and it still splits the first split again.

Hello Do you know what might be wrong if the Advanced Options doesn't give the option of split by row or column?

@Computergaga

4 жыл бұрын

Sorry, I don't know why this is.

This was a great tutorial however what if you have tons of data on one spreadsheet and you will be performing the power query multiple time is my case. I do not know how to return my split data to my original spreadsheet.

THE SPLIT TO ROWS IN THE ADVANCED OPTIONS IS NOT AVAILABLE IN OFFICE 2016

Thanks for the sharing, however I am not able to view rows option under advanced options, it showing only column

@Computergaga

3 жыл бұрын

I'm not sure why this is. Maybe it is an older version? All versions should have rows under Advanced Options.

very useful. I have a huge excel file with 100+ rows and 10+ columns. Some rows have multiple line items. Not all rows have the same set of lines- there are differences, which means some particular cell has 3 lines, some 5 lines, and some 10+. is that method showing this video also work?

@Computergaga

Жыл бұрын

Thank you. Yes, it should work as it uses the delimiter. As long as the delimiter is the same, then no worries on how many rows. But give it a try with your data.

@roniedhaka

Жыл бұрын

@@Computergaga Hi, i just tried but failed. In your example you have multiple lines in one column, the rest column is single-line text. My file has several columns with several lines in each column. Like- column A has 4 lines, Column B has 4 line, Column C has 5 lines etc. Now, I want to split column B into multiple rows but with the same split of Columns A and C lines. (each line should match next columns line)

@dimasyogapratama3930

Жыл бұрын

@@roniedhaka Hi did you manage to solve your case??please let me know, i have same problem

Hello, thank you for your video. I tried several times to do this but I am getting this message each time "Container exited unexpectedly with code 0xFFFFFFFF" . Do you happen to know the reason by any chance? Thank you in advance!

Old vision varuma? In this row separate also telme

Power Query makes many Excel task easy 😀

@Computergaga

6 жыл бұрын

It certainly does. I love it.

How can we use this function in Microsoft Excel 2013? I didn't found in From/Table Range in Data Menubar in Microsoft Excel 2013.

@Computergaga

4 жыл бұрын

In Excel 2013, Power Query is an Add-In. You can find more details here - shorturl.at/ghlT5

THE SPLIT TO ROWS IN THE ADVANCED OPTIONS IS NOT AVAILABLE IN OFFICE 2016 . How can i do it in office 2016

@Computergaga

4 жыл бұрын

I thought it was there. May have to wait for the update.

How to do this in MS Excel 2016

@Computergaga

2 жыл бұрын

Power Query is available in Excel 2016 so I would expect the same approach. I also have this video kzread.info/dash/bejne/oaiBzsWPc6W4eZM.html which provides another solution for you to try.

I need to split a cell that has pieces of data separated by an enter, something like this -This is a row of data -This is the enter -This is another row for some reason, this option won't work for me :(

@Computergaga

4 жыл бұрын

Yes, your data is in separate rows as opposed to different lines in a row. So a different technique is needed.

@Computergaga

4 жыл бұрын

As long as there is a consistency or a pattern something can be done. This video might give you ideas - kzread.info/dash/bejne/l4qJpZuBla3Jldo.html. Also Column from Examples might help.

4 жыл бұрын

@@Computergagathat's not quite what I have but I appreciate the response :)

The final step didn't work. It didn't load back to the worksheet

@Computergaga

2 жыл бұрын

Sounds like it may have loaded as a connection. On the right, you will see a Queries pane with the query. If not, click Data > Queries & Connections. Right-click the Query and click Load To. Select Table and click a cell on the sheet 👍 Or, try again and ensure at the final step to use the drop-down list and click Load To to specify a table instead of connection only.