Remove Emojis from Open Text Field

[CharleyKirton]

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

[goncalogeraldes]

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]

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]

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

[CharleyKirton]

Aww - thank you for trying!!  

Unfortunately when I said I'm using Power Query, it's actually as a dataflow (which later feeds an AI engine, so needs to remain a dataflow), so Python's not an option.  **bleep**!!  I really appreciate your help though!!

I'm now experimenting with this method: (402) Power Query: Bulk Replace Values from a table in Excel & Power BI - YouTube     

[CharleyKirton]

(Ha!  That bleep really was not a terrible expletive!!)

[goncalogeraldes]

Ohhh I hope that solution works for you! Sad I could not help tho! Take care 🙂