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 ...
Need Help
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