Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Compare and replace values in column from another table

Hi,

 

I have a table (TableA) with these columns

ProvinceDistrictCity
AroviaEastwoodWillobrook
AroviaNorthsidePynville
Belloria Greenridge
BelloriaWestfieldMapelwood
  Cordovia

 

I manually created another query table (TableB) with the below

ProvinceDistrictCity
AroviaEastwoodWillowbrook
AroviaNorthsidePineville
BelloriaGreenridgeOaksville
BelloriaWestfieldMaplewood
CordoviaRiversideCedarville


I would like to compare the City columns in TableA with TableB, and return or replace the values in TableA that match the closest, with values from TableB, in a new separate column.


For example, in TableA column, there are some City names misspelt, and some District and Province names wrongly listed as City.

How do i fix this problem using M query ?

 

Once fixed, TableA will eventually have a new column NewCity like below

ProvinceDistrictCityNewCity
AroviaEastwoodWillobrookWillowbrook
AroviaNorthsidePynvillePineville
Belloria GreenridgeOaksville
BelloriaWestfieldMapelwoodMaplewood
  CordoviaCedarville

 

Thanks in advance !

Need Help
Tips and Tricks
Tutorial Requests

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      thanks but its still not exactly what i want it to achieve.

      it doesnt call the correct values from TableB. The only other thing i can think of using the Table.AddFuzzyClusterColumn is to create a separate Transformation Table (TT), but that defeats the purpose, because that would require me to manually enter the data to match it accordingly in the TT.

      Also, Table.AddFuzzyClusterColumn is not able to differentiate between City, District and Province. Some Province and District values are in the wrong column.