Matching Text Columns

[numberpro]

Hello,

I'm often given a list of company offices, but each list may have a different spelling such as:

CHI-CHI vs CHI-CHI-DVD

What I would like to do is set up a conversion list in power query:

List:
CHI-CHI/CHI-CHI_DVD
PHI-PHI/PHI-PHI-EST

And tell power query, ok look at this conversion list, and the on Table A, check column 'Office', and where you find the first office name, replace it with the second.

I would prefer to do this in power query since obviate the need to do a vlookup prior to import into power query, and it would automate this step. Is there a way to do this?

I am running Excel 2010, and a beginner with power query. So I would prefer a solution using the built in function rather than M code; but if that's the only way, then I would try to use that too.

Thanks!

 

[Comfy]

Here you go.

And something to note, "So I would prefer a solution using the built in function rather than M code"

These are both the same thing. The GUI buttons write the M code that Power Query uses.