Replace multiple values in one column

[mtrevisiol]

Hi everyone, I'm new to the forum!

I've got some problems with power query. I have a column with some names and some of them are mispelled.

This is an example:

Name

Alex J

Alex J..

Alexander J.

Alex Jordan

Thomas

Thomas D.

Thomas D

Thomas D

This is the result I would like to see:

Name

Alex J.

Thomas D.

but when I try to replace the values more than ten times (I have 15 mispelled names), I get an error: "Internal error: An expression services limit has been reached."

Maybe this is due to the dimensions of the table? It has 42 columns and 49180 rows.

Thanks for the help!

[Anonymous]

You could Text.Uppercase the columns in both tables,which would make it easier to do a 

Table.FuzzyJoin!

--Nate

[mtrevisiol]

Thank you!

[Anonymous · Accepted solution]

I'm not sure if there is a replacement limit per column but there are several ways to solve your issue:

1- Create a new column for the Name and then write a switch or if else statement to replace mispelled names with the correct names

2- Create a table that maps the mispelled names with the correct names and then use the Name column of the new table or add add a new column in the original table with Related() or Lookupvalue() function

After doing one of the above, you can delete the original Name column so that it won't confuse report builders

[mtrevisiol]

Thank you so much, I used the second option and it worked!