Find and Replace Multiple Values

[karlitob]

Hi all,

I have 20 different tables (which are quite sizeable 100,000 records each) that relate to 50 unique counties. Unfortunately, the tables come from different data sources so the exact spelling of each of the counties is different. In an effort to create a relationship between all the different tables I am presuming that I need to ensure that all the counties are spelled the same in each of the 20 different tables so that I can link it to my unique list.

In simple terms, how do I find and replace multiple values in one table.

In more complex terms, how do I do that using Power Query so that its an automated process when I get new data into each of the 20 tables.

Hope that all makes sense. Would appreciate any replies. Thank you.


P.S. I wonder is it also an idea to create a new column and use 'if' formulaes? For instance, IF (this cell is 'County-x1',then return 'County 1',,). Though this might work how would you do this 50 times?

P.P.S. Would merge on powerquery also work. If I had the 20 ways that the county was spelled on one table - could that be merged, and then I could delete what I dont want - i.e. the other 19.

 

[sandy666]

Any example of source data and expected result in table form? Or link to excel file with these tables?

 

[karlitob]

County (Dataset 1)

County 1
County 2
County 3
etc


County (Dataset 2)
County___1
County___2
County___3


County (Dataset 3)
Group 1_County__x_1
Group 1_County__x_2


County (Dataset 4)
1County
2County
3County


Hope that makes sense. Essentially, the county is the main data field that all the other datafields in all the other databases are based on. I have no control of the data input to the tables so I'm left with having the clean the tables in each of the datasets. If I am to link the tables in power query (which I'm learning about slowly) then I am assuming that this is the main datafield that links all the other information in the tables.

Or would index and match be more suitable?



Thnk you

 

[sandy666]

Which dataset is proper? I understand the names of county are top secret

 

[karlitob]

Dataset 1.

Ah, its nothing secret. It's not even counties - just thought it was easier to explain.

 

[sandy666]

Better is add link to excel file with data and expected result
In PQ you can tryCartesian then Conditional Column with "contain"

 

[karlitob]

Hi there

Struggling to find a way to show you the excel sheet.

Where could I upload it to provide a link for you to see?

Apologies for silly question.

 

[sandy666]

there is no stupid questions

use any server to share file (OneDrive, GoogleDrive etc.) and post link to file here

 

[karlitob]

Thanks for that. Will post link when I get googledrive to show you.


In the meantime, this might explain it.

=IF(D2="County Leicestershire","Leicester",
IF(D2="County Oxfordshire","Oxford",
IF(D2="County Buckinghamshire","Buckingham",
etc


I did this for 50 counties - and it worked. But my data file is growing at a rapid pace. It worked, and worked well, but I presume there's a better way to do this. Any thoughts.

Thanks all.

 

[Mrock1]

I realise this is a long time after your post but I may have a solution if you check out my recent posts. I used conditional formatting.

If you combine my solution wit worksheet_selectionchange it’s also possible to make the common title appear on the centre row of the visible sheet. The solution I posted doesn’t do this but I have it somewhere it you’re interested.