Hello and welcome to this video tutorial from Computergaga.com. And in this video we are
going to look at how to write IF statements in Power Query. So IF functions are some of
the most useful and commonly used functions of Excel. But how can you do a similar thing
in Power Query? Let's go and find out. So I have this table on my screen with simply
some names of salespeople, some 3 different statuses of a salesperson. Depending on their
time and what they've been up to. And then their current sales of lets say this month.
And we want to pay these people some bonuses depending on different criteria that we can
run through with some IF examples in Power Query. So let's start by clicking Data and
loading this table into the Power Query Editor. So for this first example, let's imagine that
those salespeople with a status of Executive will be paid a slightly bigger bonus than
those with a Silver or Gold status. Now let's do this by clicking the Add Column tab on
the Ribbon. And then a Custom Column. There is a button provided here for a Conditional
Column. So it provides a more user-friendly way for people to create this conditional
logic. But it is limited. So I'm going to completely avoid that and look at how we ca
write this our self. So I shall click Custom Column. That will give this Custom Column
window beginning with the name of this column we're going to add. And I'm going to name
it Bonus. And then we have this big box where we can write our formula. And underneath it
a link to learn more and also this syntax checker. So so far so good. No errors detected.
On the right hand side we have the ability of adding these columns in. So that we don't
have to type them and potentially make a mistake. Now there's a few things to bare in mind when
writing IF formulas in Power Query. And probably the key thing. Or two key things. One of them
is that it is case sensitive, and everything will be written in lower case in Power Query.
Hopefully this will change in the future, but it is very fussy. So when we'e used to
writing formulas in a worksheet we don't worry too much about case. It's going to deal with
it after. Even if you write the function in lower case. Here it's a factor. The other
thing to bare in mind when it comes to IF formulas is that instead of putting commas
in between the logical test, value if true, value if false. We're going to be using the
words then and else. So for those of you who do a bit of VBA that's going to be music to
your ears. You'll be quite comfortable doing that kind of thing. Those of you just used
to worksheet formulas, this is quite a new ball game. So I'm going to type if in lower
case and then I'm just going to put my space and put in a condition. There's no brackets
here. And I'm going to ask if Status. I'm gonna double click on that column so I don't
have to type it. Is equal to and in some double quotes, Executive. Executive. Closing double
quotes. And then the word - then. In lower case. So no comma. It's if - logical test
- then and now we've got that value if true. And I want to multiply the Sales figure. So
I'll double click on that. And I'm going to multiply it by 0.15. So 15%. I then put the
word else in. Keeping it lower case. If it's not that I will multiple the sales figure,
erm by 0.08. 8%. No syntax errors have been detected. So that is our first IF formula.
If the status is executive then Sales multiply by 15% else Sales multiplied by 0.08. No brackets,
no commas. Just using the words then and else. And keeping that case sensitive. If I click
Ok. Then that column is added to the table. I have this Bonus column and there we have
our 15% if its Executive and the 10% er 8% sorry if it is Silver or Gold. There's our
first IF formula example. With that new column added you can see the M code written at the
top in the Formula Bar. And you can adjust that column up there if needed. You can also
see the new step applied in the Applied Steps area on the right hand side. Now let's see
another example of IF formulas in Power Query. Let's bring in some And and Or logic. So if
I go back to the Add Column tab and if I bring in another Custom Column. And for this column
name, I'm going to call it Bonus2. Clever I know. And then for the custom column let's
start our If formula. And this time I want to say if it's the Executive or a Gold status.
So it's going to be if the Status equals Executive or. So we can literally just type the word
or in between the two tests. Just like we can in VBA, but very different to how we would
in a worksheet formula. Now I'm going to put in Status equals Gold. then. So back to the
previous example now as we go. It's going to be Sales multiplied by, I'll keep it as
0.15. else Sales multiplied by 0.08. So the only difference here is that we've added in
that the Status can also equal Gold. Using this or logic. By simply putting the word
or between the two tests. No syntax errors detected. If I click Ok. Then we have Bonus2
added. And you can see how the Gold statuses have actually had an increase here in Bonus2,
compared to Bonus. They're now on a 15% rather than a 8%. Showing that formula working. Now
we can do the same with the And logic. You can simply just change the word or to and.
And you have that type of logic as well. Nice and easy. Much easier to do in Power Query
actually than it can be at times with worksheet formulas. Now for one more example, let's
look at using nested IFs in Power Query. So let's imagine for this final example that
I'm going to pay a different rate, different rate of bonus for Executive, for Gold and
for Silver. So now we've got three possible actions, requiring in this case a nested IF.
Now once again it's going to have similarities to a worksheet function, but the key difference
here is the lack of commas, and the lack of brackets. Let's start by adding a Custom Column.
And let's call this, I don't know, Bonus3. And then for the custom column, if. And we'll
go straight in. Status equals Executive. then. So I'm not worrying about any or or and functions
this time. The Sales multiplied by, and I'm keeping that at 15%. else. And then we go
in with the next if. Now you can press enter to start new lines in this custom column.
Just for readability. So it may make sense in an example like this. Having them on different
lines. We're now going to put if Status equals Gold then Sales multiplied by, let's say 10
percent. else. And we could go on and do another If function, and another IF function. But
we're going to finish it of there and say that if they're not Executive or Gold sorry
then we're going to multiply the Sales by 5 percent. So that we would mean they must
have that Silver membership, Silver status. With that done, we can click Ok. So here we
have if then. After the else remember that kind of brings us into the value if false.
Straight in with the next if. else and finish it off. No multiple brackets to close of our
function or anything like that. Quick syntax check. Got the green tick. We're all happy.
Click Ok. New column called Bonus3 is added. And er yes, all good. You can see that's come
back down. It's in between when we look at the Gold ones. Now it's 10%. Better than 8%,
but not as good as 15%. And we an see our logic working. We can see the M code at the
top, which we could always edit in the future rather than having to remove any of these
applied steps on the right hand side. So that was looking at how we can write IF formulas,
in a custom column, in Power Query. And especially how it may differ to IF functions that we
may write in our worksheets. I hope you found this video useful. Please check out some of
our other video tutorials on our YouTube channel. And come check us out at computergaga.com.