Split Data Into Rows Using Power Query
Вставка
- Опубліковано 5 сер 2024
- Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
In this video, you’ll earn how to use Power Query to convert a string of names and email addresses into a formatted table. This video covers how to use the Split by Delimiter into Rows and Columns features of Power Query, and how to trim blank spaces and replace values. Create a fully automated system that can be reused anytime on a new list/string of text.
If you’d like to read the accompanying blog post on my website, you can access it here: www.excelcampus.com/powerquer...
Related Videos:
Power Query Overview - Automate Data Tasks in Excel & Power BI:
• How To Automate Data T...
How to Combine Excel Tables or Worksheets with Power Query:
• How To Combine Excel T...
#MsExcel #ExcelCampus
00:00 Introduction
00:54 Split Data
06:25 Data Cleanup
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..
VERY VERY NICE! 👊🏼💥
Very clear explanation and useful! Thank you.
You are welcome, tzyy! 😀
Another food for my brain. Thank you. Love your voice.
PQ is sure a useful tool!
Fantastic. Just few clicks and mess is cleaned up. Thanks for sharing.
Thanks Sachin! Yes, cleaned up and also ready to clean up future messes with a few clicks. 🙂
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!
thank you you saved me!
Excellent tutorial!
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!
Awesome!
And
Awesome!
Awesome video! Kept putting into columns in Power BI when I needed the rows.
Thanks john. Love your voice.
Excellent video that came up in the 'for you' section. thanks Paul
Thanks for the feedback, Paul! 😀
Super se bhi upar.. magic'
You saved my life man
Awesome video Jon! Thank you!
Thank you. It was very helpful.
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 ❤️❤️❤️
Thumbs Up Great Tutorial...Thank You Jon :)
Thanks Darryl! I really appreciate your continued support! 🙂🙌
Magic learnt. Best was the last step. Future mess can also be cleaned :)
Awesome tutorial. Thank you
You helped me a lot!
I was just struggling with that and magically appeared the solution.
Thanks again!
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.
John awesome. It so much fun learning this..
OMG, life saver!
Thanks! 🙏
thanks for the tip mate! I am sure I will make use of it someday.
Amazing👍
New subscriber ! Awesome 👍
Thanks Johny! We're excited to have you joining us! 🙌
Great one Jon
Love this:)
Very Useful thanks
#tim_and_lara_toys
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!!
This is awesome! What does the "clean" option under the transform function accomplish?
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.
This is awesome Jon! Is there a limitation on how long the text string (number of characters) that is pasted into the Excel cell?
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! 🙂
Could you provide practice file to follow along. Thanks
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.
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?
Hello can we use when the delimiter different for all lines?
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!
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.
Thanks for the videos. Could power query split the data from 501021-24 into 501021 501022 501023 501024 in row?
Thanks, I don’t have the option from data range. What should I do?
Hey Mr excel can we use a email lookout instead of it?CAn we do so?
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
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.
can I do this way if I copy file from PDF file ?
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.
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?