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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nick-evans
Advocate I
Advocate I

How can I use an OR function inside an IF statement in PowerQuery?

I am using Power BI Desktop, and I have imported a table from a SQL Database.

I am under 'Data | Edit Query | Add Column | Custom Colunn', and having problems getting my formula to work.

I would like to use an IF statement that checks multiple criteria using OR, rather than a bunch of nested if-else statements.

My formula indicates 'No syntax errors have been detected', but when I click OK to generate the new column, I get an error at the top of the window that says:

Expression.Error: The name 'OR' wasn't recognized. Make sure it's spelled correctly.

 

 

=if (OR([NAME]="Nick",[NAME]="Sally")) then "true" else "false"

 

Now I read in this thread that PowerQuery is really sensitive to capitilzation (they specify 'IF' as needing to be 'if'), but if I make 'OR' lowercase, I get a different error.

=if (or([NAME]="Nick",[NAME]="Sally")) then "true" else "false"

Token Literal Expected

If I click 'Show Error' it highlights "or".

 

I have also tried using || (double pipes) but I get a different error:

=if ([NAME]="Nick" || "Sally") then "true" else "false"

Token Comma expected

If I click 'Show Error' it highlights my first pipe '|'.

 

I can successfully add an IF statement with only 1 comparison/check, so I feel like the issue is specifically related to trying to add the OR statement.

 

Example - this works:

=if ([NAME]="Nick") then "true" else "false"

 

Is it possible to use an OR function inside an IF statement in the Add Column | Custom Column window/tool?

 

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi @nick-evans,

 

Try this formula please. 

=if [NAME] = "Nick" or [NAME] = "Sally" then "true" else "false"

Reference: https://msdn.microsoft.com/en-us/library/mt186365.aspx

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
sivaghani
Employee
Employee

This maybe better

=if List.Contains({"Nick","Sally"},[NAME]) then "true" else "false"

 

bazeemuddin
Frequent Visitor

Greetings.
 
I used this to create a new col using IF and OR
 
Num_Days = IF((Tickets[Status] = "CLOSED") || (Tickets[Status] = "RESOLVED"),
DATEDIFF('Tickets'[Reported Date], 'Tickets'[Actual Finish], DAY),
DATEDIFF(Tickets[Reported Date], TODAY(), DAY))
 
Thanks for reading.
Wish you all the happiest time.
v-jiascu-msft
Employee
Employee

Hi @nick-evans,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-jiascu-msft it works I just tried it. 

Hi @v-jiascu-msft it works I just tried it. 

v-jiascu-msft
Employee
Employee

Hi @nick-evans,

 

Try this formula please. 

=if [NAME] = "Nick" or [NAME] = "Sally" then "true" else "false"

Reference: https://msdn.microsoft.com/en-us/library/mt186365.aspx

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks

nick-evans
Advocate I
Advocate I

I am trying to add a custom column, and having problems getting my formula to work.

I would like to use an IF statement that checks multiple criteria using OR, rather than a bunch of nested if-else statements.

My formula indicates 'No syntax errors have been detected', but when I click OK to generate the new column, I get an error at the top of the window that says:

Expression.Error: The name 'OR' wasn't recognized. Make sure it's spelled correctly.

 

 

=if (OR([NAME]="Nick",[NAME]="Sally")) then "true" else "false"

 

Now I read in this thread that PowerQuery is really sensitive to capitilzation (they specify 'IF' as needing to be 'if'), but if I make 'OR' lowercase, I get a different error.

=if (or([NAME]="Nick",[NAME]="Sally")) then "true" else "false"

Token Literal Expected

If I click 'Show Error' it highlights "or".

 

I have also tried using || (double pipes) but I get a different error:

=if ([NAME]="Nick" || "Sally") then "true" else "false"

Token Comma expected

If I click 'Show Error' it highlights my first pipe '|'.

 

I can successfully add an IF statement with only 1 comparison/check, so I feel like the issue is specifically related to trying to add the OR statement.

 

Example - this works:

=if ([NAME]="Nick") then "true" else "false"

 

Is it possible to use an OR function inside an IF statement in the Add Column | Custom Column window/tool?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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