Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous 

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.