cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Replacing Letters with Accents

Hi,

 

I have two lists of names that I want to merge. One list used their names with accents (like é) and the other doesn't. Since having the proper names with the accents isn't super important, I decided to replace all of the accented characters to non accented ones so the merge will work (for example every é to a plain e).

 

Is there a quicker way to do this than doing a replace value for every single instance? Because there's about 12,000 rows.

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Hey Sean,

 

probably it is more complicated and I just don't get it because of languages reasons: I would just use the "Replace Values..." in the power bi "edit queries mode". Just right click on "René" > Replace Values. You can even choose columns and just replace é to e. 

BR

rené.PNG

View solution in original post

5 REPLIES 5
Highlighted
Helper I
Helper I

Hey Sean,

 

probably it is more complicated and I just don't get it because of languages reasons: I would just use the "Replace Values..." in the power bi "edit queries mode". Just right click on "René" > Replace Values. You can even choose columns and just replace é to e. 

BR

rené.PNG

View solution in original post

Highlighted
Frequent Visitor

Highlighted

or this:
https://docs.microsoft.com/en-us/powerquery-m/text-replace 

 

you can use:

New column = Text.Replace([column name] , "á", "a")  

Highlighted
Anonymous
Not applicable

Ah thanks! For some reason I thought that replace value only worked if you were replacing the whole value.

Highlighted
Frequent Visitor

Hello,

You can create a function in powerquery that converts all column. 

Open a blank query e paste this: 

 

(Texto as text) =>
let
    ListAccents = 
		{
		{"à","a"},
		{"á","a"},
		{"â","a"},
		{"ã","a"},
		{"ä","a"},
		{"è","e"},
		{"é","e"},
		{"ê","e"},
		{"ë","e"},
		{"ì","i"},
		{"í","i"},
		{"î","i"},
		{"ï","i"},
		{"ò","o"},
		{"ó","o"},
		{"ô","o"},
		{"õ","o"},
		{"ö","o"},
		{"ù","u"},
		{"ú","u"},
		{"û","u"},
		{"ü","u"},
		{"À","A"},
		{"Á","A"},
		{"Â","A"},
		{"Ã","A"},
		{"Ä","A"},
		{"È","E"},
		{"É","E"},
		{"Ê","E"},
		{"Ë","E"},
		{"Ì","I"},
		{"Í","I"},
		{"Î","I"},
		{"Ò","O"},
		{"Ó","O"},
		{"Ô","O"},
		{"Õ","O"},
		{"Ö","O"},
		{"Ù","U"},
		{"Ú","U"},
		{"Û","U"},
		{"Ü","U"},
		{"ç","c"},
		{"Ç","C"},
		{"ñ","n"},
		{"Ñ","N"}
		}
in
    Text.Combine(List.ReplaceMatchingItems(Text.ToList(Texto), ListAccents))

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors