Split cell into rows base on line feed carriage return is not working

[alonc]

Hello 

I have a column type Text, where cells may contain several values:

Text1 Text2 Text3

Text1 Text2

I'm using the split by delimiter and it is not working, meaning the cells aren not splitted

Can someone assist me?

[watkinnc]

Splitting by just line feed worked for me.  The formula produced by Power Query was:

= Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Session name/s", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Session name/s")

--Nate

[dufoq3]

Hi @alonc, I see that you've selected only Line Feed #(lf), try to select Carriage Return and Line Feed #(cr)#(lf).

[alonc]

I tried this, it is not working.

I also thought maybe the problem is that some of the values are null or empty

So, I filtered those values, still not working 🙁

[dufoq3]

Could you upload your file i.e. to google drive and share a link with us? (Don't forget to set public permissions), because #(lf) works with your example...

[alonc]

Excel file that contain columns with cells with several strings in some cells 

[dufoq3 · Accepted solution]

It works with #(lf)

I've connected query to your google drive link:

v1

let
    Source = Web.Contents("https://drive.google.com/uc?export=download&id=107-v_GTess0mdB25dfazed5f_OVWRKt3"),
    Content = Excel.Workbook(Source),
    Table1_2_Table = Content{[Item="Table1_2",Kind="Table"]}[Data],
    #"Split Column by Delimiter" = Table.SplitColumn(Table1_2_Table, "Session name/s", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Session name/s.1", "Session name/s.2", "Session name/s.3", "Session name/s.4", "Session name/s.5", "Session name/s.6", "Session name/s.7", "Session name/s.8", "Session name/s.9", "Session name/s.10", "Session name/s.11", "Session name/s.12", "Session name/s.13", "Session name/s.14", "Session name/s.15", "Session name/s.16"})
in
    #"Split Column by Delimiter"

v2 same as wathinnc's

let
    Source = Web.Contents("https://drive.google.com/uc?export=download&id=107-v_GTess0mdB25dfazed5f_OVWRKt3"),
    Content = Excel.Workbook(Source),
    Data = Content{[Item="Table1_2",Kind="Table"]}[Data],
    Transformed = Table.ExpandListColumn(Table.TransformColumns(Data, {{ "Session name/s", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv) }}), "Session name/s")
in
    Transformed