Replace multiple values in bulk

[absherzad]

Hi everyone,
I have a large dataset. One of the column named "CityName" includes values in different format e.g. "Herat", "Hirat", "Heraat"; all of them stand for "Herat".
The question: I am looking for an efficient method meeting the mentioned situation to unify them properly using Power Query techniques.

Screenshot

I think one method is to extract all the unique values for the column "CityName". Next make a lookup table by adding another column next to the extracted unique values including the correct value for the corresponding extracted unique value. Then either add a helper column and use EXCEL VLOOKUP, or use POWER QUERY MERGE approach overcoming the issue.

 

[absherzad]

Please see the screenshot for better clarification.

 

[Matt Allington]

The screen shots don't work, but that doesn't matter. There are 2 techniques. First as you describe using a table merge at the end. I prefer this if there are likely to be a lot of new variants. Otherwise you can just manually select the column and do multiple "replace values" to fix each scenario. If you do this, I recommend renaming the steps so it is clear where each replace occurs.

 

[absherzad]

Thank you for your response.
In my case manually repeating "replace values" is not practical.
What is the proper approach to achieve the following scenario using power query?

1. First load the large dataset consists of many columns into power query.
2. Then use remove duplicates step on "CityName" column to extract the distinct values, and create a separate table e.g. named "tblLookup" for this extracted column. But I do need the original dataset for additional transformation steps.
3. Add a complete blank column into the "tblLookup" table, and then add it to excel or to the data model. Then user manually enters the correct values into the blank column for any of the corresponding unique values. The benefit is whenever new data appended to the original dataset the new unique values will be reflected to the tblLookup table.
4. When the original dataset completely preprocessed, add it to the data model.
5. Finally merge the tblLookup and the original dataset.

For me steps 2 and 3 are tricky.

 

[Matt Allington]

This is what I would do.
create a staging query that has the raw data
From there, create a new query that returns a distinct list of the values you want to replace, load to Excel
Use my self referencing tables trick to add the replacement values. Self Referencing Tables in Power Query - Excelerator BI
once this is working, you can join this table to the main table and extract the replacement values into a new column, deleting the original.

This and many other tricks are covered in my online Power Query training here if you are interseted => Power Query & Power BI Training