How to replace multiple inconsistent values with 1 same value

[Yannyqi]

Hi everyone,

I am new to Power BI and currenlty looking for a solution to replace multiple inconsistent values with 1 same value. eg. one of the columns named "Address" includes values in a different format e.g. "Japan", "Jap", "JP", "JPN", "Japan, Osaka"; all of them refers to "Japan" and I would like to standardize by changing it to "Japan".

May I know is there any simpler way to replace all these different values to Japan efficiently? Instead of using the "Find and Replace Values" method to replace the values 1 by 1?

Any help would be really appreciated.

Thank you very much in advance.

[mahoneypat · Accepted solution]

Please see this article.  Basically, you need to create a list of lists to be used inside a step with List.ReplaceMatchingItems().

Multiple replacements or translations in Power BI and Power Query – The BIccountant

Pat

[v-yingjl · Accepted solution]

Hi @Yannyqi ,

If the values of columns are like a sentence within the above strings, you can refer the article mentioned by @ mahoneypat to replace values at once.

If the values of columns are like a single character such as JP, JPN etc. for each row, perhaps create a query to replace it at the same time is better:

 

= Table.ReplaceValue(#"Changed Type",each [Address],"Japan",Replacer.ReplaceText,{"Address"})

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Yannyqi]

Hi @v-yingjl ,

Thank you very much, i will try it out with this 😀

[mahoneypat · Accepted solution]

Please see this article.  Basically, you need to create a list of lists to be used inside a step with List.ReplaceMatchingItems().

Multiple replacements or translations in Power BI and Power Query – The BIccountant

Pat

[Yannyqi]

Thank you so much! I will try it out 😀