cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
donaldm
Helper III
Helper III

Text Forced Enter/Return

I have an excel file as datasource and within the excel file, there is one column that contains long texts that have forced return within the text.

 

When pulling in the text field into PowerBI table visual, the forced return is shown as "_x000D_".

 

I'm surprised not very much has been discussed here on the forum. I tried using the Query Editor's "Replace Value" function selected "Carriage Return" under Advanced Options --> "Replace using special characters".  But it didn't work.

 

Aside from going into Excel and use "Clean" formula to remove the forced returns, can the fix be done within PowerBI?

 

 

1 ACCEPTED SOLUTION
donaldm
Helper III
Helper III

Actually after many tries over 3 days, I finally found a solution to my issue, so I am here to share:

 

In Query Editor: 

Value to Find: _x000D_

Replace With: #(cr) --> Special Character for Carriage Return

View solution in original post

4 REPLIES 4
donaldm
Helper III
Helper III

Actually after many tries over 3 days, I finally found a solution to my issue, so I am here to share:

 

In Query Editor: 

Value to Find: _x000D_

Replace With: #(cr) --> Special Character for Carriage Return

View solution in original post

Anonymous
Not applicable

Hi @donaldm 

I couldn't understand how it solved the problem.
My data has "_x000D_ written at the end.
Can you help me?

Hi @Anonymous 

In the Query Editor, select the column with the special characters.  Then under the Transform tab, select Replace Value.

 

A menu pops up, and at the bottom of the menu, expand "Advanced Options", and select "Replace using Special Characters".

 

Then follow the solution post in this thread.

 

Hope this helps.

Anonymous
Not applicable

Thank you so much @donaldm !!!!!! You saved my life 😀

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors