These are the best explanations you can find on the internet. Thank you Jon.

Power Query wins the day again. If only more people knew that Power Query existed. Good work 👍

Jon, your explanation(s) of Excel capabilities / features is second to none. If one didn't know any better, they could easily assume you were the creator of Excel. Many thanks for all of your time and efforts - Keep the great videos coming!!!

I am pleasantly surprised to notice that even after 5k views and over 300 likes, there is not even a single dislike. This is rare nowadays.. Good job Jon. Keep up the good work..

Awesome tutorial. Thank you

Very clear explanation and useful! Thank you.

@ExcelCampus

Жыл бұрын

You are welcome, tzyy! 😀

Another food for my brain. Thank you. Love your voice.

This is so great! I have been searching for how to apply the power query steps to new data/worksheets. I didn’t know it was that simple as to copy and paste over the previous data. Thank you very much!

VERY VERY NICE! 👊🏼💥

Fantastic. Just few clicks and mess is cleaned up. Thanks for sharing.

@ExcelCampus

3 жыл бұрын

Thanks Sachin! Yes, cleaned up and also ready to clean up future messes with a few clicks. 🙂

thank you you saved me!

PQ is sure a useful tool!

Excellent tutorial!

Awesome! Awesome! And Awesome!

Amazing technique....Your Dad just challenged you and you came to the top..this is beautiful and can be used in many smiliar situations WOW..PQ is here to change land scape of working with data...for sure

Awesome video! Kept putting into columns in Power BI when I needed the rows.

Thanks john. Love your voice.

You saved my life man

You helped me a lot! I was just struggling with that and magically appeared the solution. Thanks again!

Awesome video Jon! Thank you!

Hi Jon. Excellent lesson. Power Query is awesome! Can't get enough practice doing transformations and clean-up. Thanks for sharing :)) Thumbs up!!

Thank you ❤️❤️❤️

Thank you. It was very helpful.

Excellent video that came up in the 'for you' section. thanks Paul

@ExcelCampus

4 ай бұрын

Thanks for the feedback, Paul! 😀

Thumbs Up Great Tutorial...Thank You Jon :)

@ExcelCampus

3 жыл бұрын

Thanks Darryl! I really appreciate your continued support! 🙂🙌

John, I love your videos. You are so smart. Even though I don't have a need to use many of your fantastic methods I really like learning about them. It's nice to know all the things excel is capable of. Looks like this power query could be used as a Macro.

OMG, life saver!

@ExcelCampus

3 жыл бұрын

Thanks! 🙏

John awesome. It so much fun learning this..

Magic learnt. Best was the last step. Future mess can also be cleaned :)

thanks for the tip mate! I am sure I will make use of it someday.

Super se bhi upar.. magic'

Amazing👍

Great one Jon

Love this:)

New subscriber ! Awesome 👍

@ExcelCampus

3 жыл бұрын

Thanks Johny! We're excited to have you joining us! 🙌

Thank you for the video. What if I need to split multiple lines separated by line breaks and also with space in between in a row? Do I still use split column--> by delimiter? Thank you!

This is awesome! What does the "clean" option under the transform function accomplish?

Great Video and instructions. Question: if I create a power query, render sent it file to someone outside my organization (i.e. a client), are they able to update the power query if new information is entered? Thank-you!!

My biggest question is whether there is a way to do this with more advanced, nested data with more of a structure to it. I'm hoping to find a tool that can parse a products sheet with all their options and attributes all together in a single, custom formatted column into a more database-friendly format.

Hello can we use when the delimiter different for all lines?

Could you provide practice file to follow along. Thanks

This is awesome Jon! Is there a limitation on how long the text string (number of characters) that is pasted into the Excel cell?

@ExcelCampus

3 жыл бұрын

Great question, Rick! Yes, the number of characters in a cell is limited to 32,767. When you paste a string longer that into a cell, Excel will automatically truncate it to 32,767 characters without warning. So it's good to check the end of the strings to make sure everything was pasted in. To give some context, the example I used only had 2,858 characters. You can use the LEN function in Excel to return the number of characters in a cell. If the result of LEN is 32,767 then you probably have a string that is too long. The nice part about this technique with Power Query is that you can put additional strings in the cells below on the sheet that contains the source data (pasted data). Power Query will do the split into rows for each cell in the column, and stack the data in the same output table. You can also use this technique to add to the existing list in the future when you get new lists/strings of data. I explain more about this in the blog post for this video. Here is a link. www.excelcampus.com/powerquery/split-into-rows/ Here is a link to a help with more info on the cell limits and other limits of Excel. support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 I hope that helps. Thanks again and have a nice day! 🙂

Thanks, I don’t have the option from data range. What should I do?

Thanks for the videos. Could power query split the data from 501021-24 into 501021 501022 501023 501024 in row?

Hey Mr excel can we use a email lookout instead of it?CAn we do so?

hi, i tried the same method but my advance option does not have option for row. it only has no of columns to split into.

Very Useful thanks #tim_and_lara_toys

This question is somewhat related. But how would you split data from multiple columns into multiple rows, with multiple subsets of columns from a specific row? For instance, I have a form that collects the various interventions that schools use with their students - and each intervention has its own specific characteristics tied to it (Type - Academic/Behavior; Subject - Math/Reading; Tier Levels - 1/2/3; and Grade Levels K/1st/2nd/3rd/4th/5th/6th/7th/8th/9th/10th/11th/12th). So if a school uses 1 intervention then there would be 5 columns (+ demographic info), 2 interventions would mean 10 columns; 3 interventions - 15 columns. How do I make each group of 5 columns for each specific intervention its own row (along with the demographic info of the school submitting) in Excel?

Excel is kludge upon kludge upon kludge, but I guess it's better to have the kludge available than not be able to do it at all. I've always done this sort of thing by spending some time in a text editor to get something I can import as a CSV file. If you can remember where everything is or you do this sort of thing enough, this would certainly be a little easier.

can I do this way if I copy file from PDF file ?

Hi, Can you make a video how to transpose every n rows with more than 1 columns in query? For example data repeated every 4 rows with 2 columns

Awesome! How could I split two rows of my email address into two columns, but put them in one row with different cell using power query? Tq.

Good but this can be done by pasting data in MS Word and replace comma (,) with new line (^p) and then paste the data in Excel and then perform text to columns to remove unwanted texts.

Not getting from table/ range option in data option in my excel

What version of Excel is this?

Need query on something.. How to contact you?

you did the 2nd delimiter split for the ">" symbol in a SECOND step again. However, i have a scenario where my data has multiple delimiters like the following symbols: "/", a "," and "~" symbols But i've not seen an option, in that same PowerQuery window, where I can list the multiple delimiters in one field (or step). Can i do that?