Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JuliaFab
New Member

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:

JuliaFab_0-1641565668042.png

 

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

2 ACCEPTED SOLUTIONS
jennratten
Super User
Super User

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")

 

View solution in original post

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")

View solution in original post

8 REPLIES 8
JuliaFab
New Member

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.

jennratten_0-1641572771847.png

 

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

Thank you for your help 🙂

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")
jennratten
Super User
Super User

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")

 

Thank you! it worked!!!

Thank you for the suggestion!
unfortunately it didn’t work..

 

is there a way to find the character for certain which separates the text?

 

JuliaFab_0-1641569111511.png

 

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.

 

jennratten_0-1641570527005.png

 

jennratten_1-1641570581264.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.