Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Unknown Special Character in Query

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...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    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.)

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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?