Forum Discussion
If and or statements (simple)
Hi! I am struggling with a simple issue that I am unable to solve in Power Query. The problem is that when I am adding a custom column with the following logic:
if [#"Wage: Range - Ceiling"] > 50 or [#"Wage: Fixed"] > 50 or [#"Wage: Up Until"] > 50 or [#"Wage: Starting From"] > 50 then "Monthly"
else if [#"Wage: Range - Ceiling"] > 10000 or [#"Wage: Fixed"] > 10000 or [#"Wage: Up Until"] > 10000 or [#"Wage: Starting From"] > 10000 then "Annual"
else "Hourly"
the column returns just "Hourly" and "Monthly" and disregards the "Annual" statement completely. I am assuming there is something wrong with the or statement as all the data types are correct (numbers) and when I put only one column (without or) everything works. I have also tried to put the logic the other way around (if less than 50 then Hourly else Monthly), but then it just returns all values as Hourly.
Here is the problem visualized:
Can anyone point out to me where is the issue?
Thank you in advance!
Hi Anonymous
You need to place the most restrictive conditions first. Otherwise, if Wage Fixed is greater than both 50 and 10000, the first condition will be met ("Monthly") and the code won't check for the "Annual" condition
if [#"Wage: Range - Ceiling"] > 10000 or [#"Wage: Fixed"] > 10000 or [#"Wage: Up Until"] > 10000 or [#"Wage: Starting From"] > 10000 then "Annual" else if [#"Wage: Range - Ceiling"] > 50 or [#"Wage: Fixed"] > 50 or [#"Wage: Up Until"] > 50 or [#"Wage: Starting From"] > 50 then "Monthly" else "Hourly"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
2 Replies
Hi Anonymous
You need to place the most restrictive conditions first. Otherwise, if Wage Fixed is greater than both 50 and 10000, the first condition will be met ("Monthly") and the code won't check for the "Annual" condition
if [#"Wage: Range - Ceiling"] > 10000 or [#"Wage: Fixed"] > 10000 or [#"Wage: Up Until"] > 10000 or [#"Wage: Starting From"] > 10000 then "Annual" else if [#"Wage: Range - Ceiling"] > 50 or [#"Wage: Fixed"] > 50 or [#"Wage: Up Until"] > 50 or [#"Wage: Starting From"] > 50 then "Monthly" else "Hourly"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers