Forum Discussion

leolapa's avatar
leolapa
Brass Contributor
Aug 31, 2022
Solved

Figure out character showing as space on Power Query

After running an API GET request method I get a set of data whose snippet is shown by the below screenshot:

However when I run a `Merge Queries` of the `Contract` column with another table so I can get each correspondent translation to Portuguese it'll only match a few items, as shown below:

After checking the other table's contents every correspondent `Contract` was there, but then after copying the entire `Contract` column from the API dataset and pasting it to a blank Excel spreadsheet I realized that the spaces displayed on Power Query are actually some unrecognizable characters displayed as "?" inside a diamond:

I've already tried the `Clean` tool but it won't work. I also tried other think such as `Trim` and `Replace Values...` space by something else and no glory.

Something tells me I need to figure out which character is actually being imported instead of space so I can try to replace it on Power Query, but the question is: how can I do that? How can I turn those "?" into readable characters in Excel?!

Leonardo

  • leolapa 

    Most probably it's always the same combination instead of space. It depends where you'd like to clean the text, in Excel or in Power Query. In any case that's like substitute of this combination on space.

     

    In Excel you may check by UNICODE what are these characters after splitting the text.

  • leolapa 

    Most probably it's always the same combination instead of space. It depends where you'd like to clean the text, in Excel or in Power Query. In any case that's like substitute of this combination on space.

     

    In Excel you may check by UNICODE what are these characters after splitting the text.

  • DexterG_III's avatar
    DexterG_III
    Iron Contributor

    leolapa Any chance you can share either the workbook or a table which includes the special characters?  

     

    Dexter

Resources