Replace value in one column based on text in another column , same row

[veronica_zappp]

I'm an M language beginner, and don't quite follow how to replace values. I've seen other suggestions in the community for similar issues but not quite what I need.

Here is the scenario, I have a series of data with Country, ODACountryISO3, _ISO3. As you can see in red, I am missing values for the middle column. I am also missing values in the _ISO3 column. I need to manually clean this up such that I only use the ODACountryISO3 information for my map. 

I need to tell Power Query where it sees a null in ODACountryISO3, look for a specific country name, and replace the null with a specific code. So, if null in ODACountryISO3 AND Country(receiving) is China, replace the null with CHN. I need to do this for about 20 scenarios.

What is the best way to go about this? I prefer doing this in PowerQuery rather than in the raw data for a number of reasons.

Thank you to this community for all the help!

[HotChilli]

A few different ways to do this sort of thing.

Most solutions in Power Query add columns and then remove columns rather than in-place replacement so you could add a custom column and hardcode all the cases: (as in your example)

if null in ODACountryISO3 AND Country(receiving) is China, replace the null with CHN else

if ....

or merge with a table which holds all the correct codes

or look up the values in the master table, possibly using Table.SelectRows

[veronica_zappp]

Thanks, the reason I have two separate ISO3 columns is because neither are complete, and in some cases, null is in both columns. Thank you for explaining the basic concept of "add column" and "remove column" in M... it helps put things in perspective. I'll see if I should find a better way of merging the data at the outset. Country names are terrible Keys for left inner joins/merges.