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.
Benötigen Sie einen VBA Programmierer?
Wir als exact construct programmieren mit einem Team von rd. 20 Mitarbeitern seit über 10 Jahren Excel-Tools. Wir sind ein Nischenanbieter der spezialisiert auf Makros/VBA-Codes ist. Daneben unterstützen wir auch als 3rd Level Support die IT-Abteilungen rund um Probleme bei MS Office (Excel, Word, PowerPoint, etc.).
Haben Sie ein Excel-Problem? Benötigen Sie einen Makro-Programmierer? Rufen Sie uns unverbindlich an +41 52 511 05 25 oder kontaktieren Sie uns via Kontaktformular
