Split by line breaks in Power Query

2015-10-16
Ken Puls
Categories: Excel, Power BI, Power Query

Some more savvy Excel users know that you can break text onto multiple lines in a cell by pressing Alt+Enter mid entry. Today’s post explores how we can split by line breaks in order to break these types of cell contents into multiple columns.

Set up the data

To start with, let’s set up some simple data:

In cell A2, type “Text” and press Enter
In cell A3 type “This” –> Alt + Enter –> “is” –> Alt + Enter –> “text” –> Enter

The result should look like this:

And now we’ll go and pull it in to Power Query:

Select the data –> create new query –> From Table

Split by Line Breaks

At this point, you’d certainly be forgiven for thinking that only the first line was pulled in. But if you select the cell, you’ll see in the preview window that all the data is there:

So let’s try and split it up.

Right click the Text column –> Split Column –> By Delimiter

Unfortunately, there is no line break or carriage return option in the dialog, which means that you’ll need to pick “Custom”, and enter the special character for a Line Feed:

Even worse, with entering this, Power Query is overly aggressive when you click OK. It assumes that this is special text, so escapes it to text, and appends some commands that actually mess you up:

Notice how we have two columns with nothing in the second. What gives there?

To correct this code, we need to modify the formula in the formula bar to do two things:

Undo the escaping that Power Query did on our #(lf) entry, and
Remove the code that is telling which columns to import

So first, we need to replace:

"#(#)(lf)"

with

"#(lf)"

And second, we need to remove this completely:

,{"Text.1", "Text.2"}

And the results are much better:

The Good/Bad News

The bad news is that currently it’s a bit painful to do this. The good news is that it can be done, and the better news is that Power Query is constantly being updated. I’m sure it won’t be long before they give us an easier to use/more discoverable mechanism to make this work.

Other Special Characters

Should you need them, here are three special characters that you can refer to in Power Query:

Line feed: #(lf)
Carriage return: #(cr)
Tab: #(tab)

24 thoughts on “Split by line breaks in Power Query”

Maxim Zelensky on October 16, 2015 at 10:27 am said:

Thanks, specially for the last part.
pmsocho on October 21, 2015 at 1:51 pm said:

Try this method with data like this:

A3: This is text
A4: This is one more text

Of course replace space with Line feed.
Your solution will not work properly on this data 🙂
List of expected columns is necessary because PQ is counting the number of columns based on the first row of data 🙁
Bill Szysz on October 22, 2015 at 2:40 pm said:

Hi Ken,
I don't know how to say it... this will not work properly. :-((
I'm sure that last argument (list of columns) is necessary unless you have the longest sentence (max number of #(lf) ) in first row of data. If not, the result will be incorrect.:-((
We need write the step to create the list of columns we need and then use it as a proper argument.

Regards
Ken Puls on October 31, 2015 at 2:05 pm said:

Yes, you're correct guys. That's what I get for only testing with a couple of pieces.
Bill Szysz on November 2, 2015 at 3:04 am said:

Hi Ken,
Further to my previous post, here is one of possible ways.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChType = Table.TransformColumnTypes(Source,{{"Data", type text}}),
HeadersList = List.Transform({1..List.Max(List.Transform(ChType[Data],each List.Count(Text.Split(_,"#(lf)"))))}, each "text"&Text.From(_)),
Result = Table.SplitColumn(ChType,"Data",Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv),HeadersList)
in
Result

where "Data" is the name of column with text containing "#(lf)"

Regards
Yekcim on November 4, 2015 at 1:57 am said:

Hi Ken,
Because I have a hard time remembering the special characters, I usually start out with a standard delimiter from the dialogbox, say "=" . Then I go into the formulabar and replace just the delimiter, and not the hyphens, by typing Shift+Enter. This will acually cause the formula line to have a line break with no special character symbol being visible. To me this is more intuitive, besides the fact that it took me a while to discover that Alt+Enter in excel is the same as Shift+Enter in PQ. The result however is the same as your method.

Keep up the good work!
Yekcim
Stephen Frost on March 9, 2016 at 8:41 pm said:

I am using Power Query in Excel 2013 to get all .txt files from a folder. The problem is that when the content of the files is combined together, there is no carriage return and line feed between files. The text of the next file begins immediately after the previous file, with not even a space between. The result is that the first new row of the new file doesn't begin until after the first cr + lf. I think the problem may be that each .txt file does not end with a cr + lf.

How do I fix this, so that each file begins on a new row in the combined output?
Stephen Frost on March 13, 2016 at 5:01 pm said:

To my previous post about missing cr+lf between files from folder:
I solved the problem by creating a custom column to convert each binary file to a table. Formula for the custom column:
Table.FromColumns({Lines.FromBinary([Content],null,null,1252)}).
Ken Puls on March 13, 2016 at 10:38 pm said:

Hi Stephen,

Sorry I didn't get to this (upside down with conference and travel). Thanks for posting back that you found a solution!
Eric Giroux on February 10, 2017 at 8:45 pm said:

Hi Ken,

Would you happen to know why when I concatenate colums with tabs in between the resulted text is well formatted in power query but when displayed in a pivot table it seems to be stripped and all the text is glued toghether?

Thanks.
Ken Puls on February 13, 2017 at 10:05 am said:

Hi Eric,

The only thing I can think of is that PivotTables don't recognize the tab as a printable character. You may want to come up with a method that pads with spaces in between instead of tabs.
EdH on April 19, 2017 at 7:47 am said:

Amazing 18 months later this is still an issue. Bit me yesterday.
Ken Puls on April 19, 2017 at 9:18 pm said:

EdH, I'm curious on this (don't even remember writing the post, to be honest!). I just went into Power Query on the Excel 2016 Fast insider build. When I use my original data set, right click the column and choose to Split by Delimiter, Power Query automatically identifies that it is the #lf character, identifies the number of columns based on the highest occurrence of #lf characters in the column and... just works.

Which version of Excel are you on? If you're on Excel 2010/2013, maybe try updating to a newer version of Power Query. If you're on Excel 2016, are you on subscription, and what build are you using?
Umut on November 10, 2017 at 7:32 am said:

again you saved my day !
Phil Whitacre on March 26, 2019 at 9:08 am said:

Hi Ken,

I love your blog. I'm trying to use Power Query to Transform a spreadsheet and combine data from multiple columns into a single cell with a carriage return between each of the column inputs. Here is an example of code:

= Table.AddColumn(#"Renamed Columns", "Merged", each Text.Combine({"ServerDate: ", [ServerDate],"#(cr)#(lf)","CaptureDate: ", [Priority], "/7/2", [UserScore], "19 2:52:45 PM"}), type text)

The Power Query out looks like:
ServerDate: 3/7/2019 4:52:52 PM
CaptureDate: 3/7/2019 2:52:45 PM

The output is good when do the transformation in Power Query, but when I Close & Load to convert to an Excel Spreadsheet, the carriage return disappears.

The Excel Output looks like:
ServerDate: 3/7/2019 4:52:52 PMCaptureDate: 3/7/2019 2:52:45 PM

How can I retain the carriage return when converting to Excel?
Ken Puls on March 26, 2019 at 9:20 am said:

Hi Phil,

Believe it or not, it's probably working, it just doesn't look like it. Select the cells and go to Excel's Home tab --> Wrap Text. On my screen it works nicely.
Enrico on July 31, 2019 at 10:51 pm said:

Still doesn't work properly to this day. It does recognize the delimiter and sets it up correctly, BUT if there are dots or more obvious delimiters inside the same cell, and it defaults to that one instead of line feed, you're out of luck, and have to go through the whole process described here. Thanks, by the way 🙂
Ken Puls on August 1, 2019 at 9:09 am said:

I think it starts at the top and works it's way down (but could be corrected there). Regardless, we don't want the AI to be TOO smart here, or we wouldn't have jobs. LOL!
Ryan D Pope on May 23, 2020 at 9:49 am said:

Ken, I love your book! Thanks for this post. I modified your query a little bit and got the results that I wanted by clicking split into rows. I did not have to modify the M code at all. It works perfectly. Thanks!
Antony Pihut on October 30, 2020 at 9:26 am said:

Thank you Ken!
Where are the buttons 'Like' and 'Share'?
I love to tell the world who helped me.
Rebekah on November 30, 2020 at 12:06 pm said:

Hi Antony, we're glad you found the article helpful. We also appreciate the comment about having Like and Share buttons and will see if this is something we can add to the blog. Thanks for reading, Rebekah (part of the Excelguru team)
David on March 16, 2021 at 8:29 am said:

Saved me a lot of time! Thank you!
Ann W on April 2, 2021 at 4:11 am said:

I'm not sure when things changed, but at least in Power Query in Power BI, there is now a special characters check box at the bottom of the page and if you pick Line Feed, it just works.
Ken Puls on April 2, 2021 at 9:42 am said:

Hi Ann, I'm not sure when it changed either... the team has made a LOT of improvements since this was originally posted. 🙂