cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
evalromf
Helper I
Helper I

AND IF in conditional column

Hi,

 

I'm trying to create a new column that should be populated with a certain number based on the contents of several other columns.

 

What I'm looking for is something like this:

 

If [COUNTRY] equals Kazakhstan AND IF [COMPANY] equals Alltel, then "1"

Else If [COUNTRY] equals Kazakhstan AND IF [COMPANY] equals VIP, then "2"

 

But i cannot find such a possibility in "Add conditional column". (see picture) 

 

AddAdditonalColumn.JPG

 

 

 

 

 

 

And when I try to use the Advanced Editor, I cannot find any syntax that covers this scenario.

 

Does anyone have a solution for this?

 

 

53 REPLIES 53
MarcelBeug
Community Champion
Community Champion

You need to add some paranthesis as and has higher priority than or.

In your case the formula should be built like (a=x or a=y) and (b = m or b = n) etcetera.

Without paranthesis, a=x or a=y and n=m or b=n is equivalent with a=x or (a=y and b=m) or b=n.

So without parenthesis, you can think of all (groups of) ands evaluated first and the ors being in between (groups of) ands. 

 

Specializing in Power Query Formula Language (M)

Thanks MARCEL! I'll give it a shot in a moment!

So, this is what I have and I get an "error" in the new custom column...

 

if ([Course Name] = "Managing Information" or "Promoting Positive Workplace Behaviour") 
and ([#"Organisation / Portfolio"] = "Bio21 Australia Ltd" or "Bio21 Australia Limited"
or "CAVAL" or "Grattan Institute" or "Kendall Hall" or "Melbourne University Publishing Ltd"
or "Nossal Institute Ltd" or "University House" or "Australian National Academy of Music Ltd"
or "Melbourne Theatre Company" or "MU Student Union Ltd") then "Exclude" else "Include"

 

This the error:

 

Expression Error.PNG

Have you tried putting the follwing below?

 

if ([Course Name] = "Managing Information" or [Course Name = "Promoting Positive Workplace Behaviour")

And then repeat for each instance?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

What was the response to this. 

 

I have similar situation for a custom column, i need to have values

 

if col1=a1 and col2 =a2 then '50' 

else col1=b1 and col2=b2 then 100 

 

and so on for 20 values

 

how to resolve this

Please see content from @gilbertiq, that would suit your needs

Hi @Anonymous

 

You can create a new Custom Column in the Power Query Editor with the following syntax

 

if [col1] = "a1" and [col2] = "a2" then 50
else if [col1] = "b1" and [col2] = "b2" then 100
else if [col1] = "c1" and [col2] = "c2" then 200
else 999




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

That worked!! Thanks you guys for helping me out!!

Glad you found a solution!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

I have not, I'll give that a shot! Thanks!

Tanks for the assistance, and sorry for my late feedback ... 

 

I've managed to solve this in a completly different way - I added the ID in a data preparation stage, i.e. before the data is fed into Power BI. This is actually a more neat way to manage the IDs, as I need the data to be properly cleansed for other applications as well (i.e. not just in Power BI).

KHorseman
Community Champion
Community Champion

You can just use the regular Add Custom Column button and write out a function for the column. Your example pseudocode is missing the final else, by the way, so I made something up.

 

if [COUNTRY] = "Kazakhstan" and [COMPANY] = "Alltel"
then 1
else if [COUNTRY] = "Kazakhstan" and [COMPANY] = "VIP"
then 2
else 0




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors