Excel Power Query IF Statements
Power Query if statements are slightly different from writing IF formulas in Excel, but once you get the hang of them, they are quite easy. In fact, the Power Query versions of IF OR, and IF AND are even easier than the Excel equivalents.
IF Statements using Conditional Column
On the Add Column tab of the ribbon click Conditional Column.
The dialog window opens (see below) with an easy step and click on the menu to help you construct the if statement.
After clicking OK, a new column Discount will be added.
In the scenario above, we have only used a few of the options available in the Conditional Column dialog box.
Other operators are available:
- Equals
- Does not equal
- Begins with
- Does not begin with
- Ends with
- Does not end with
- Contains
- Does not contain
Also, we can create as many if statements as we want by clicking the Add rule button. The if statements execute in turn, so if the first if statement is not triggered, it will go to the second, then the third, and so on. However, if the first if statement is true, the remaining logic is skipped ignored.
IF Statements using Custom Column
Power Query if statement is easy to create with the Conditional Column GUI. But you can’t use the Conditional Column for every if scenario. Sometimes you’ll have to use the Custom Column dialog box and write them yourself.
In which case you should be aware of the key differences to the Excel IF Statement, namely:
- Power Query formulas are case sensitive
- The ‘if’ in Power Query is lower case
- Instead of commas separating the value_if_true and value_if_false arguments we have the words then and else (in lower case).
- null means blank in Power Query
Add Column tab > Add Custom Column
The dialog box opens and you have to give your column a name and then type your formula into the ‘Custom column formula:’ field.
Basic IF condition
Syntax:
if [if-condition] then [true-expression] else [false-expression]
Custom column formula:
= if [Status] = “Old” then “50%” else “10%”
In English the above formula reads:
If the Status is Old then return “50%” else “10%”
Click OK to add the formula for Discount column.
IF statement with OR Logic
The OR operator works analogically to AND. This condition recognizes either of the conditions.
Syntax:
if [if-condition1] or [if-condition2] then [true-expression] else [false-expression]
Custom column formula:
= if [Product] = “Product A” or [Product] = “Product B” then “Nike” else “Adidas”
In English the above formula reads:
If the Product is Product A or Product B then the brand is “Nike” else “adidas”
Click OK to add the formula for Brand column.
IF statement with AND Logic
This is use when you need to include two conditions in your statement.
Syntax:
if [if-condition1] and [if-condition2] then [true-expression] else [false-expression]
Custom column formula:
= if [Status] = „Old“ and [Price] > 500 then „Free“ else 150
In English the above formula reads:
If the Status is Old and Price is greater than 500 then the Delivery Fee is “Free” else “150”
Click OK to add the formula for Delivery Fee column.
If-conditions with Common Operators
To make your if-conditions a bit more advanced you can use common operators. Common operators can be:
- = Equals
- <> Is not equal to
- > Greater than
- >= Greater than or equal to
- < Less than
- <= Less than or equal to
Learn more about Power Query by browsing other articles regarding Power Query on this website.
Do you need a VBA programmer?
As exact construct, we have been programming Excel tools with a team of around 20 employees for over 10 years. We are a niche provider specializing in Macros/VBA codes. In addition, we also support the IT departments as 3rd level support for problems with MS Office (Excel, Word, PowerPoint, etc.).
Having an Excel problem? Do you need a macro programmer? Call us without obligation on +41 52 511 05 25 or contact us via the contact form
