Getting rid of the TILDES in a Spanish text

jazzista

Member
Joined
Jan 4, 2017
Messages
50
Reaction score
0
Points
6
Excel Version(s)
Office 365
Hello: Is there an easy way to get rid of the tildes within a text using PQ? I was able to take out the tildes in regular excel with a small table and the using substitute function however is there a way to take the tildes out of a word in Power query? Attached is the file and here is the formula in regular excel
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(U6,$AD$7,$AE$7),$AD$5,$AE$5),$AD$4,$AE$4),$AD$6,$AE$6),$AD$8,$AE$8) Thanks in advance
á
a
é
e
í
i
ó
o
ú
u

á
a
é
e
í
i
ó
o
ú
u
 

Attachments

  • tildes.xlsx
    14.9 KB · Views: 8
Hola!

The easiest way to do this with Power Query, and probably the most optional, would be to use the "Replace value" option from the UI and basically call that operation multiple times. Is not as bad as you think, it's pretty straightforward and you can get it from the "Transform" group inside the "Home" tab. Just select the column that has the values that you want to replace and you can then do a "find and replace" operation similar to how you'd do it in word, excel or any other office application.

Now, of course that you can create your own pattern to do BULK replace operations, but those will run quite slow when you start hitting tens of thousands of records in which a workaround of a reference table would be needed which would add unnecessary complexity to your solution. I'm attaching that way to this reply, but, again, it'll run quite slow and even when optimizing it with Table.Buffer it'll still run pretty slow when compared to the way described in the first paragraph.
 

Attachments

  • tildes.xlsx
    23.1 KB · Views: 2,576
Hola Miguel: I see: the easier way to do it is replace every vowel with the tilde using the replace value : replace-->á with a replace--> é with e and so on. Got it. I love that function that you created but how could use it? Thanks for the help
 
you should be able to see a new table in the sheet. That table has 2 columns: OldText and NewText, which can be translated to TexToFind (OldText) and ReplacerText (NewText). Just fill that table with the letters or words that you want to replace and then refresh the output query to make it happen and it'll just work.
 
Back
Top