Forum Discussion
Unknown Special Character in Query
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.)
Do you have a sample JSON we can try to reproduce this error with?
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 !!
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.)
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?