Power Query: Split Column by Delimiter and Split into Rows

So, you've got a bunch of data all in one cell. You need to not only get it out, but it needs to be in rows, stacked in a single column.

With this new Get & Transform (Power Query) feature it's EASY!
Split Column by Delimiter into Rows

This video has 2 examples:
1. A plain example
2. A real world example

You'll also see a left outer join ... and a sword.

Download the file here:

#PowerQuerySplitIntoRows #SplitRowsByDelimiter #SplitDataIntoRows

My book: Guerrilla Data Analysis 2nd Edition

[sasavienne]

Almost all my Power Query skills are the outcome of your videos. Thanks Oz.

[joannpaules]

I've just enlisted another Power Query fan and introduced your videos to him. And other data warrior is born. :-)

[excelisfun]

That is awesome!!! Love the confetti!!

[mannysingh]

Great channel along with Excel is fun! Instantly subbed! please keep showing us the wonders of Excel :)

[rockguitarist]

Great video and fun to watch! Loved the samurai and fire parts. Clear explanations and you've earned a new subscriber!

[lenzypetty]

Awesome....exactly what I was trying to do months ago....I knew you'd have the answer! Wish I had found your video sooner.

[barttitulaerexcelbart]

Thank you for showing new applications for new functionalities in Power Query.
You always learn something new: for example now that you can work with a table without a header.

btw: did you cut the confetti with the sword? ;) Keep using it...

[ekelly]

BRAVO! I will definitely use this at work.

[mazarata]

Great tutorial, is a very realistic situation you showed us here - and procuring people wit bad headake. Luckily with this video, is has a happy confetti end :-). Thanks a lot.

[mohamedchakroun]

Thanks oz for this video very helpful tricks

[vagnermonteiro]

AMAZING !!!! Thank you very much dude!

[TreUnplugged]

Nice job Sir. Do you have any tutorials explaining how to add line breaks to a list within a cell?

Example - Cell has various drawer dimensions: Top drawer - 23 x 14 x 4 Middle drawer - 23 x 14 x 3 Bottom drawer - 23 x 14 x 2. Is there a way besides Shift+Enter per line to produce the format below?

Top drawer - 23 x 14 x 4
Middle drawer - 23 x 14 x 3
Bottom drawer - 23 x 14 x 2

I have 1000s of items, all with dozens of various attributes and NO delimiters.

Any help very much appreciated. Thx - TG

[barttitulaerexcelbart]

Oz, the query works also if you add another instrument (competence) to an existing student. Thats nice. So in the end, the result schould be that you can play good music with the students assigned to each group/class. I made this overview with a Pivot table, all relevant data in rows, nothing in value field. But that makes me think of another (theoretical) question:
Would it be possible to get the same overview were you started, but then instruments in stead of names? So instruments comma seperated? So is it possible to "do it the other way around" (just like unpivot).
Let's Rock!

[excelisfun]

Be careful with confetti and fire in the same video before everything goes up in smoke !!!

[prabhuprasaddevanaboina]

I need exactly this. But from table and have 200 columns like above. Could you help me?

[master.swordz]

can you please help explain what does split by delimiter #(lf)?

[mattmatt]

Can you please show how to use power query to create list of all possible combinations in one-dimensional cutting-stock problem ?
Thank you
Matt

[Serraomomma]

in my "Advance Options" Rows is not a choice. Could this be because I have multiple data in four other columns that will need splitting ? This file I have is a bit of a challenge.

[TreUnplugged]

Is there anyone who can help splitting by a numerical mask? I have several lines of data (no delimiters) in one column. Example: Top Drawer Dimension - 15 x 10 x 5 Middle Drawer Dimension - 15 x 10 x 3. If I can split the cells after every sequence of (number x number x number), it'd be a huge help.

Trying to get here:
Top Drawer Dimension - 15 x 10 x 5
Middle Drawer Dimension - 15 x 10 x 3

Thanks for any help!

[entertainmentgalaxy]

Date Inv# Amount

1/3/2018 292 £171.40
13/03/2018 396 £165.00
19/03/2018 411 £165.00
18/04/2018 448 £790.50
14/05/2018 492 £653.00
07/06/2018" 525 £615.00

How to split Multiple columns into rows. Only one column does work text to column in PQ at once as shown in your video as well.
For example as shown above, all dates were in a single column with line break and same with inv # and amount.

Desired Result: same as shown above but in each cells.

Can you please help me out with this problem? i hope u can..

Thanks

Haider Ali