Conditional Logic: IF statement for Conditional Columns

If you come from Excel, you’ve probably seen or heard about the IF statements and its new sister the IFERROR.

I remember the first time that I saw a conditional chain like the picture below:

It looked WAY better as a diagram than as an Excel formula, nevertheless – it worked just fine inside of Excel.

The question is….how do Conditionals work in Power BI / Power Query? do we have an IF function? maybe an IFERROR? THIS is the blog post where I’ll cover this topic.

What type of Conditional Logics do we have in Power BI / Power Query?

In essence, we have 2 types of M language expressions (also known as “keywords”):

• if expression –  essentially the same behavior as the IF function that we have in Excel
• error handler –  ALMOST the same as the IFERROR from Excel, but even better.

In this post I’ll cover the if expression, how it works and how to use it and in the next post in this series I’ll cover the error handler or IFERROR in Power BI / Power Query.

IF Statement in Power BI / Power Query

The case: Imagine a simple table like this:

what we would like to do is create a new column that should have the total tax of the transaction. Some lines are taxable and some others are not, so the condition is this:

• if the column [Taxable?] is equals to “Yes” then multiply the value from the [Tax] by the value from the [Amount] column

Remember that Power BI / Power Query is a user-oriented tool, so it has this really nice and unique User-interface that provides a rich User-eXperience.

If you don’t want to learn any code, you can use the UI to accomplish almost 80% of what you want to do when it comes to Data Preparation inside of Power Query.

We’ll use UI oriented method first.

Creating a Conditional Column using the Power BI / Power Query user interface

if you go to the “Add Column” menu, you’ll see the “Conditional Column” button:

When you click it, you’ll get a new window called the “Add Conditional Column” window and it looks like this:

It’s pretty cool! but let me tell you how it works.

On the top left you’ll see a “New Column name” and this is where you’ll input the name of the new column.

Then we see a row that starts with “if” and that’s where I’ve entered the conditional logic, but there’s once piece missing and that’s the output!

We’re only able to enter either a static value or select a column. We don’t have an option to enter a formula here, so we can’t move forward with this. What we need to do first is create that column and refer to that column once it’s in our table.

Let’s just go ahead and close this window for now and go back to the Power Query Window.

We can select both columns and then do a simple multiplication by going through the “Add Column” menu:

but there’s still one more thing that we need to do and that is round that value. So with the output of the previous operation, we select that column and then go to the “Transform” menu and do a Round from there:

and with that value now in the table, we can go back to the Conditional Column and just enter it like this:

After this, we can simply remove the “Multiplication” Column and that’ll give us the following result:

which is exactly what we’re after.

One of the caveats of this whole process is that it relies on a lot of layers or steps because we’re not able to input the formula right from the “Add Conditional Column” window.

What if we could do all of these 4 steps:

1. Multiply the columns
2. Round the value from that column “Multiplication” column
3. Create a Conditional Column
4. Remove the “Multiplication” column as its no longer needed

in just 1 step instead…could it be possible? It sure is! Let’s find out how

Creating a Conditional Column using the Power BI / Power Query Custom Column window

This method does require you to know M code, but it could save you A LOT of time and will help you minimize your code so it’s not “bloated” and so difficult to read and understand after you’re done with it.

Instead of going with the “Conditional Column” from the “Add Column” menu, we’ll go with the “Custom Column”:

and now, thanks to all the updates that Power Query has been getting, it’s easier to input the formula that we need directly into the Custom Column window. Just look at this:

The formula above does the multiplication and the rounding but, what about the if statement?

In Power Query everything is case sensitive. In this case, the if statement should look exactly like this:

if [#”Taxable?”] = “Yes” then
Number.Round( [Tax] * [Amount], 2)
else 0

Notice how all parts of the if statement are colored in blue. In order to make a nested IF, we would use the if again after the else – again, everything is case sensitive so keep that in mind.

The end result will look like this:

where everything was done in just 1 step.

Be sure to check out the next parts in the series where I’ll go over:

• Logical Operators and nested conditionals
• IFERROR in Power Query