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

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

1 REPLY 1
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.