Forum Discussion

CharleyKirton's avatar
CharleyKirton
Frequent Visitor
3 years ago

Remove Emojis from Open Text Field

Hi all!

 

I have over 800k rows of open text data from a range of sources including social media.  I need to remove (pesky) emojis.  One single message contains 55 different emojis like the person just mashed the interface with their palm, whhhyyyy?!

 

Does anyone know how I can do this in Power Query without needing to use replace values over and over again?  I can't find an elegant solution so really hoping someone else has an idea!

 

Thanks very much!!

Charley

Need Help

6 Replies

  • goncalogeraldes's avatar
    goncalogeraldes
    Super User
    3 years ago

    Hi CharleyKirton ! To remove emojis from a text string in Power Query, you can use the Text.Replace function. You can use a regular expression pattern to match any emoji in the string, and replace it with an empty string. Here's an example of how to do that:

     

    let
    // Define the text string that contains emojis (just as an example in this case)
    text = "This string contains some emojis 😀😃😄😁😆😅🤣😂🙂🙃😉😊😇",

    // Use a regular expression to match any emoji in the string
    emojiPattern = "[\uD83C-\uDBFF\uDC00-\uDFFF]+",

    // Use the Text.Replace function to replace all the matched emojis with an empty string
    result = Text.Replace(text, emojiPattern, "")
in
    result

     

    The regular expression used in this example will match any Unicode characters in the range U+D83C to U+DFFF, which covers all the emojis in the Unicode standard. The Text.Replace function will then replace any matched characters with an empty string, effectively removing the emojis from the text.

     

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes

    • CharleyKirton's avatar
      CharleyKirton
      Frequent Visitor
      3 years ago

      Hi goncalogeraldes 

      Thanks for getting back to me!

       

      I'm struggling to make it work at the moment:  I put your code into a blank query with some emojis in the text string and it still returned the emojis, so perhaps they're emoticons or something instead?!

       

      Where are you getting the uD83C - uDBFF etc bit from?  I could only find ones like U+1F300 - U+1F5FF (which features the below wave image) but I tried those with and without the + included and still got the symbols returned so not sure what I'm doing wrong.  Any ideas?

       

      let
          // Define the text string that contains emojis (just as an example in this case)
          text = "This string contains some emojis 🌌🌊⬛️",

          // Use a regular expression to match any emoji in the string
          emojiPattern = "[\uD83C-\uDBFF\uDC00-\uDFFF]+",

          // Use the Text.Replace function to replace all the matched emojis with an empty string
          result = Text.Replace(text, emojiPattern, "")
      in
          result
       
       
      Returned:

       

      • goncalogeraldes's avatar
        goncalogeraldes
        Super User
        3 years ago

        Hi CharleyKirton ! I am also struggling to get this to work with both emojis and emoticons but I did get to do it with another aproach:

        let
    // Define the input table
    input = Table.FromRows({{"This text string contains some 😀 emojis."}, {"Another text string with 😂 some emoticons."}, {"This string contains some emojis/emoticons 📣🎍🍪🍾🎾"}}, {"TextColumn"}),
    #"Run Python script" = Python.Execute(
        "# 'dataset' holds the input data for this script
        #(lf)#(lf)filter_char = lambda c: ord(c) < 256
        #(lf)dataset['TextColumn'] = dataset['TextColumn'].apply(lambda s: ''.join(filter(filter_char, s)))",
        [dataset=input]),
    #"Expanded Value" = Table.ExpandTableColumn(#"Run Python script", "Value", {"TextColumn"}, {"Without_Emoji"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Value",{"Name"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Without_Emoji", type text}})
in
    #"Changed Type"

         

        Please keep in mind that since this solution is based on a Python script in can increase the refresh times so please test this in your dataset before adopting it as a solution.

         

        Hope this answer solves your problem!
        If you need any additional help please @ me in your reply.
        If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
        Thanks!

        You can also check out my LinkedIn!

        Best regards,
        Gonçalo Geraldes