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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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