Replacing Unicode character/non printable characters

[Anonymous]

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

[edhans · Accepted solution]

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:

1. Use Find/Replace
2. Put anyting in the Find box. Doesn't matter. You just want the formula to be built for you.
3. 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.