Unknown Special Character in Query

[Anonymous]

I have been trying to clean some JSON data in Power Query which is being requested by using the Slack API; however, I have one field that has an unknown character that is not being displayed in Power Query but even though it's not being displayed when you try to use the value as a key with another table it will not work since the character is being included but not displayed.  

 

If you copy and paste the data in excel or Open Refined you get these results, which highlight the special character.

if you take a deep look at the JSON you don't find anything new, more than \n and * look to be being decoded correctly: 

"text": "*Build Break Form* submission from 󠀠<@UH0FX7JGN>󠀠\n\n*What type of break is it?*\n󠀠Flaky Test󠀠\n\n*If cause is other, please explain.*\n󠀠󠀠\n\n*Ticket Created*\n󠀠XXX-NNNNN 󠀠\n\n*If a broken test, what is the PTID?*\n󠀠372󠀠\n\n*Please provide any additional information.*\n󠀠󠀠\n\n*Link to the build notification*\n󠀠<https://...>",

Another example just in case:

"text": "*Build Break Form* submission from 󠀠<@UMDC9SGGL>󠀠\n\n*What type of break is it?*\n󠀠Infrastructure problem󠀠\n\n*If cause is other, please explain.*\n󠀠󠀠\n\n*Ticket Created*\n󠀠<https:...XXX-NNNNN> 󠀠\n\n*If a broken test, what is the PTID?*\n󠀠󠀠\n\n*Please provide any additional information.*\n󠀠󠀠\n\n*Link to the build notification*\n󠀠<https://....>",

Again if you paste it in excel it shows as a box with a question mark if you open the line or just a box.󠀠󠀠box with a

So it looks like I have some special character that it's not \n or * since the first image shows that both are being interpreted correctly, but I still have a special character not allowing me to use the data ... I have tried Trim and Clean, they did not work ... I have tried replace with all of the special characters that are included in Power Query default and they did not work. So I would like to be able to remove the special character by replacing it by since I can not see it it has become a huge pain. 

Any ideas of how to find this special character hidden in my data and remove it by using Power Query ? (By the way I am using Power Query on Power BI)

[AlexisOlson]

Do you have a sample JSON we can try to reproduce this error with?

[Anonymous]

You should be able to replicate with this short JSON and just importing it to Power Query and looking at the message.text column.

Thank you !!

https://onblackboard-my.sharepoint.com/:u:/g/personal/mateo_tavera_blackboard_com/Ea3TPHyqwCdDtExCMJ...

[AlexisOlson · Accepted solution]

Thanks. It appears to be a Tag Space Emoji when copied from Excel.

You can either remove it explicitly with a step like this:

= Table.ReplaceValue(#"Prev Step",
    Character.FromNumber(917536), "",
    Replacer.ReplaceText, {"messages.text"}
)

or a much more general method of stripping everything except standard characters and line breaks:

= Table.TransformColumns(#"Prev Step",
    {{"messages.text", each Text.Select(_, {" ".."~", "#(lf)"}), type text}}
)

(Space and tilde are the first and last in the Printable Characters block.)

[Anonymous]

IT WORKED !!! THANK YOU SO MUCH !! I had been trying to debug this for 3 days without any clues about what was going on. How did you identify the tag space emoji ? which tool did you use?