Data Validation with if statement

[Anonymous]

Hi all, 

In my table there column with Regions and column with countries.

I'm looking for a way to validate if countries match to the region as often we are getting data where for example region is India but country Vietnam. 

added a custom column with if statement and it works for one region but when I'm trying to add more I'm getting right output (Great/Not so great) for first region but error for the rest. 

I wrote something like that 

if[EMPLOYING_REGION_COMBINED] = "India" and [EMPLOYING_COUNTRY] = "India" then "Great"
else if [EMPLOYING_REGION_COMBINED] = "Americas" and [EMPLOYING_COUNTRY] = "Argentina" or "Brazil" or "Canada" or "Chile" or "Colombia" or "Mexico" or "United States" then "Great"
else if [EMPLOYING_REGION_COMBINED] = "Poland" and [EMPLOYING_COUNTRY] = "Poland" then "Great"
else if [EMPLOYING_REGION_COMBINED] = "Vietnam" and [EMPLOYING_COUNTRY] = "Vietnam" then "Great"

else "Not so Great"

my table looks like

Region         Country       Expected Output    what I'm getting

India             India                  Great                   Great 

India             Vietnam             Not so great        Error

Americas       Chile                  Great                    Error

Americas       Mexico               Great                    Error

Poland           Poland               Great                    Error

Thank you all in advance

Marcin

[PaulOlding · Accepted solution]

Hi @Anonymous 

That looks like M code from Power Query, correct?

The issue is the list of or values.  You need to respecify the column name each time.

So...

if [EMPLOYING_REGION_COMBINED] = "India" and [EMPLOYING_COUNTRY] = "India" then "Great"
else if [EMPLOYING_REGION_COMBINED] = "Americas" and ([EMPLOYING_COUNTRY] = "Argentina" or [EMPLOYING_COUNTRY] = "Brazil" or [EMPLOYING_COUNTRY] = "Canada" or [EMPLOYING_COUNTRY] = "Chile" or [EMPLOYING_COUNTRY] = "Colombia" or [EMPLOYING_COUNTRY] = "Mexico" or [EMPLOYING_COUNTRY] = "United States") then "Great"
else if [EMPLOYING_REGION_COMBINED] = "Poland" and [EMPLOYING_COUNTRY] = "Poland" then "Great"
else if [EMPLOYING_REGION_COMBINED] = "Vietnam" and [EMPLOYING_COUNTRY] = "Vietnam" then "Great"

else "Not so Great"

[Anonymous]

Hi Paul, 

Thank you very much.