Split by line breaks in 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:

SNAGHTMLa795c48

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:

image

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:

image

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:

image

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:

  1. Undo the escaping that Power Query did on our #(lf) entry, and
  2. 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:

image

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)

18 thoughts on “Split by line breaks in Power Query

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

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

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

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

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

  6. 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)}).

  7. Hi Stephen,

    Sorry I didn't get to this (upside down with conference and travel). Thanks for posting back that you found a solution!

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

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

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

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

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.