Forum Discussion
Replace multiple values in one column
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!
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
4 Replies
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
Thank you so much, I used the second option and it worked!
Thank you!
