Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Splitting Rows by Backspace

Hello! I'm creating a query from PDF and run into a problem. query read separate rows as one, i need to separate them:   By copy pasting values in excel and using =unichar() I found t...
Need Help
  • jennratten's avatar
    jennratten
    4 years ago

    Are you sure your character is a backspace?  That won't produce a new line.  This is how you can split a cell to new rows by a carriage return/line feed (character 13):

    Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("#(cr)#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1")

     

  • jennratten's avatar
    jennratten
    4 years ago

    Interesting - what about this?

    = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type2", {{"Details.1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Details.1")
Anonymous's avatar
Anonymous
Not applicable
4 years ago

thank you!

The characters are just as you said previously: 

"140731865#(cr)#(lf)TELETRANSMISSION INWARD#(cr)#(lf)1022ZAPS019128140717705"

 

I tried typing following formula, still didnt work..
= Table.ExpandListColumn(Table.TransformColumns(#"Changed Type2", {{"Details.1", Splitter.SplitTextByDelimiter("#(cr)#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Details.1")

 

is there anything else i could try? 

 

 

jennratten's avatar
jennratten
Icon for Super User rankSuper User
4 years ago

Also, when you right-click on the column header and choose Split Column > By Delimiter, are any of the fields populated?  This is what tipped me off that it may just be a line feed without carriage return that is needed.  Which is strange because I used your sample text and it showed both.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    no it doesnt auto populate filled, perhaps there are too many options..

    Thank you for your help 🙂