IF functions are some of the most popular formulas in Excel, and in this blog post we will look at how to write an IF function in Power Query.
There are some key differences between IF function in Power Query and their worksheet equivalent, but you can pick them up quickly.
In these examples we will use the sample data below, already loaded into the Power Query Editor.
We have some salespeople, their sales figure and a status that they have attained.
Writing an IF Function in Power Query
In our first example, we will pay our salespeople a bonus of 15% of their sales, only if they are of an Executive status. They will get 8% of their sales if not “Executive”
We will to add a new column to the table, so click the Add Column tab of the Ribbon.
In Power Query there is a Conditional Column button which presents a window to make writing conditional statements user friendly. Good for beginners.
However this is limited and I feel you are much better served by learning to write If statements purely. So we will click the Custom Column button.
The Custom Column window appears. Firstly we can enter a name for the column – for example Bonus.
We will then write the If statement in the Custom column formula box provided.
Underneath the box is a message informing you of any errors in your syntax. And to the right, a list of the columns from our table. We can use this list to enter the columns into our formula instead of typing them (and potentially making silly mistakes, so I’m a fan).
We will enter the following formula.
= if [Status] = "Executive" then [Sales] * 0.15 else [Sales] * 0.08
There are a few things you need to know when writing If statements in Power Query.
- The formula is written in lower case.
- It is case sensitive and there is a difference between “If” and “if”.
- There are no commas. Instead the words “then” and “else” are used to separate the test, the value if true and value if false (this will be familiar to VBA users).
- Spaces are typically entered between the words to make it more readable.
Notice there are no errors detected in our formula.
With the new column added and still selected, you can see the M code (name of the language) in the Formula bar. And you can edit it up here in the future.
It is also added as an applied step on the right and can easily be removed also.
So that is our first If statement example. Let’s do another one.
Using And and Or in your If Statements
You may have used the AND and OR functions in your IF formulas before in Excel to create more complex logic. This can be difficult when users are just beginning with formulas.
Well the good news is that it is actually easier in Power Query.
For this example, we will pay our salespeople a bonus of 15% is their status is “Executive” or “Gold”, otherwise they get 8% of their sales.
We can use the following formula.
= if [Status] = "Executive" or [Status] = "Gold" then [Sales] * 0.15 else [Sales] * 0.08
All we need to do was enter the word “or” between the two conditions. Wonderfully easy.
The same would apply with the And logic. Simply enter the word “and” between the conditions.
It is also possibly to have more than two conditions.
Writing Nested IF Functions in Power Query
For one last example, we will look at write a nested If function in Power Query. Once again this is nothing to fear, and is actually quite simple.
For this example we will pay a different bonus depending on whether they are of “Executive”, “Gold” or “Silver” status.
The key differences again when writing nested ifs in Power Query is that it must be in lower case, and also there will be no commas or brackets.
We can use the following formula.
= if [Status] = "Executive" then [Sales] * 0.15 else
if [Status] = "Gold" then [Sales] * 0.1 else [Sales] * 0.05
It was not necessary to write this on two lines, but it does helping reading nested ifs.
So If functions are a little different in Power Query than in worksheet formulas, but they are simple to learn and a nice step into the world of writing M code in Power Query.
Many thanks for your blog,, it´s really of great help.. (so the youtube sessión.. .please go on with it 🙂 ).
I was wondering whether is possible to help me with this:
my first condition is: Value to be null if that condition is true then enter the second condition:
Country = Spain then “ES”
Country = Portugal then “PT”
else keep it as it.
I have build following status:
= Table.FromRecords(Table.TransformRows(RenCol,(row) => Record.TransformFields(row, {“Store”, each if row[Value]= null and row[OU]=”Spain” then “ES” else if row[Value]= null and row[OU]=”Portugal” then “PT” else _})))
but I was wondering whether it could be possible to avoid to repeat twice the 1st condition “if row[value]= null” as a part of the general condition.
Many thanks again for all your contribution to PQ 🙂
all the best.
Jadara.