Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
This maybe better
=if List.Contains({"Nick","Sally"},[NAME]) then "true" else "false"
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
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
Thanks
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |