Transformation for all other values

[Geminidad]

My file has a column containing a two letter code representing the group. There are about 25 unique codes.

To combine this file with one from another source, I need to align these values with the codes used in that other file.

Code CB is replaced with Dentist, CI is replaced with IC, CP with Patient and all others with STD.

The first three replacement are tstraightforward, but how can I handle that last one without having to do it one by one for the remaining 22 unique values?

- wildcards do not work

- I can filter out the first three but then I am left with a subset and cannot figure out how to bring back the other records.

How can I setup a flow that works?

Thnx

[DOLEARY85 · Accepted solution]

Hi,

can you create a new column in power query to convert all the codes prior to a join e.g

if [CODE] = "CP" then "Patient" else if [CODE] = "CI" then "IC" else if [CODE] = "CB" then "Dentist" else "STD"

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Geminidad]

Thank you for the quick reply. Looks like a good solution, I will put that in my workflow for this file. 👍