The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to Solution.
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")
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.
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")
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?
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.