Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Data Validation with if statement

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

1 ACCEPTED SOLUTION
PaulOlding
Solution Sage
Solution Sage

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"

 

View solution in original post

2 REPLIES 2
PaulOlding
Solution Sage
Solution Sage

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
Not applicable

Hi Paul, 

 

Thank you very much. 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.