Forum Discussion
Replacing Unicode character/non printable characters
My data source is Sharepoint folder which has xlsx data. Created a table in Power BI in which letters of the same word are flowing to next line. If i copy paste the excel data to notepad, i see the characters � �. I have tried replacing these characters with advance editor but still letters of same word flow to next line. Please help
That is probably where someone has pressed CTRL-ENTER in a cell to get a line wrap.
Go to that column and replace the carraige return and/or line feed as follows:
- Use Find/Replace
- Put anyting in the Find box. Doesn't matter. You just want the formula to be built for you.
- Leave the Replace box empty, and press OK.
Now in the formul bar you might have this (my column was called "Test", and I searched for "blah" to get it going.
Table.ReplaceValue(Source,"blah","",Replacer.ReplaceText,{"Test"})Replace "blah" with one of three solutions:
Table.ReplaceValue(Source,"#(lf)","",Replacer.ReplaceText,{"Test"}) Table.ReplaceValue(Source,"#(cr)","",Replacer.ReplaceText,{"Test"}) Table.ReplaceValue(Source,"#(cr)#(lf)","",Replacer.ReplaceText,{"Test"})Sometimes is is just a line feed, sometimes it is a carraige return, and sometimes it is both. You won't know until it replaces the return and puts the data back on one row.
1 Reply
That is probably where someone has pressed CTRL-ENTER in a cell to get a line wrap.
Go to that column and replace the carraige return and/or line feed as follows:
- Use Find/Replace
- Put anyting in the Find box. Doesn't matter. You just want the formula to be built for you.
- Leave the Replace box empty, and press OK.
Now in the formul bar you might have this (my column was called "Test", and I searched for "blah" to get it going.
Table.ReplaceValue(Source,"blah","",Replacer.ReplaceText,{"Test"})Replace "blah" with one of three solutions:
Table.ReplaceValue(Source,"#(lf)","",Replacer.ReplaceText,{"Test"}) Table.ReplaceValue(Source,"#(cr)","",Replacer.ReplaceText,{"Test"}) Table.ReplaceValue(Source,"#(cr)#(lf)","",Replacer.ReplaceText,{"Test"})Sometimes is is just a line feed, sometimes it is a carraige return, and sometimes it is both. You won't know until it replaces the return and puts the data back on one row.