Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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?

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

6 Replies

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        dufoq3
        Icon for Community Champion rankCommunity Champion
        2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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