Forum Discussion
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 that text is separated by Backspace.
I tried splitting rows by Tab and any other option there is.
I also tried by:
Splitter.SplitTextByDelimiter(Character.FromNumber(10), QuoteStyle.None)
I'm running out of ideas.
Please help.
Thank you in advance,
Julia
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")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")
8 Replies
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")Yes, there are a few ways. A quick and easy way is to click on a cell that has a line break, copy the text that appears in the preview, open a blank query and paste the text you copied into the formula bar and press enter. The text in the formula bar will change to display the characters.
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?
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.
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")