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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
61 | |
53 | |
27 | |
16 | |
10 |