Split Data Into Rows Using Power Query

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

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?

Very clear explanation and useful! Thank you.

Super se bhi upar.. magic'

Thank 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?

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

Awesome tutorial. Thank you

Thank you. It was very helpful.

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

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

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.

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

You saved my life man

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!

thank you you saved me!

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! 👊🏼💥

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.

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

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

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

Power Query wins the day again.

If only more people knew that Power Query existed. Good work 👍

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

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

Amazing👍

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

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.

Need query on something.. How to contact you?

John awesome. It so much fun learning this..

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

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

Awesome video Jon! Thank you!

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

Love this:)

You helped me a lot!

I was just struggling with that and magically appeared the solution.

Thanks again!

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

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.

Thanks john. Love your voice.

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

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

Excellent tutorial!

PQ is sure a useful tool!

Great one Jon

Or if you prefer a "simple" formula:

=LET(_textToSplit,B7,_separator,";",
_textLen,LEN(_textToSplit),
_noOfOccurences,_textLen-LEN(SUBSTITUTE(_textToSplit,_separator,"")),
_seqChars,SEQUENCE(_textLen),
_findSeparator,INDEX(SORT(_seqChars*(MID(_textToSplit,_seqChars,1)=_separator)),SEQUENCE(_noOfOccurences,1,1+_textLen-_noOfOccurences)),
_seq,SEQUENCE(_noOfOccurences+1,1,0),
_startPos,IF(_seq=0,1,INDEX(_findSeparator,_seq)+1),
_endPos,IF(_seq=MAX(_seq),_textLen+1,_findSeparator),
_splitText,MID(_textToSplit,_startPos,_endPos-_startPos),
_comment,"----task specific function below, generic split function above!---",
_lenSplit,LEN(_splitText),
_emailStart,IFERROR(SEARCH("<",_splitText),_lenSplit),
_splitToCols,SWITCH({1,2},1,TRIM(LEFT(_splitText,_emailStart-1)),IF(_lenSplit=_emailStart,"",MID(_splitText,_emailStart+1,_lenSplit-_emailStart-1))),
_splitToCols)

That should do it! The first half of the function will work for general splits (return _splitText instead of _splitToCols), the last few rows I added in for this special circumstance!

What version of Excel is this?

Awesome!
Awesome!
And
Awesome!

New subscriber ! Awesome 👍