Logical Operators and Nested IFs in Power BI / Power Query

In the previous post I showed you guys how to create a conditional column in Power BI / Power Query using the UI and then just using the Power Query Formula language. In this post we’ll go over the available conditional operators and how to do Nested IFs in Power BI / Power Query.

What are Logical Operators?

You’ve probably seen them sometime in DAX or in the Excel formula language and some of those are:

but how do you write them in the Power Query formula language? we already know that we can only use them inside a Custom Column, but how will that look like?

Logical Operators in Power BI / Power Query

Let’s do a few tests to see how these operators work. I have this simple table that I’ll use asan example:

One thing to take in consideration before you try these by yourself, Power Query formula language (also known as M), is case sensitive.

Test 1: Using the AND operator

We’ll be creating a new column to check if the value in this column is greater than 8 AND less than 25. That will look like this using a Custom Column:

[Number] > 8 and [Number] < 25

and the result of that will look like this:

Note how the output is logical value, either a TRUE or a FALSE.

Test 2: Using the OR operator

Now we want to create a new column that will test if the value is either less than 15 or greater than 25. That into M code will look like this:

[Number]< 15 or [Number] > 25

and the result of that will look like this:

and yes! it gives us the correct answer again

Test 3: Using the NOT operator

For this final test, let’s find all the values that are NOT below 25. That into M code will look like this:

not ([Number] < 25)

something really important about this formula is that I have the initial test in parenthesis, and what not does is simply shift the logical value to the opposite of that. The result of that will look like this:

Common use cases of the logical operators

You may have seem these logical operators in use before. If you’ve ever done a filter in a table, check out what the formula bar says:

Yes – when it comes to filters, the logical operators can sometimes be used. Specifically when you need to select multiple values or parameters for a filter expression. Apart from this, these logical operators are commonly used in IF statements, so let’s take a look at them.

Nested IF statements

Now that we know what the logical operators are and how to use them, let’s try and use them in a more practical way. Doing a recap on how if statements work in Power Query, you have the following formula:

if <test> then <result if true> else <result if false>

The result of the <test> must be a TRUE or FALSE, or in other words, a logical value. With that in mind, for the <result if true> or the <result if false> you can absolutely use another if statement without any issues.

Practical scenario: Calculating the Shipping cost of an order

Imagine that we have this table:

and from it we need to calculate the Shipping cost based on this logic:

Translating that from M into just plain English:

if the Account of the order is Prime AND the weight is under 5kg AND the amount is higher than 100, then the shipping cost for the customer will be 0 (FREE SHIPPING!)
if the previous doesn’t occur, then if the account is Prime AND the amount is over 200, then the shipping cost is 0 (FREE SHIPPING!!)
if neither of those occur, then just use a standard formula to calculate the shipping which is Weight times 1.25

The result of that is:

Pretty simple, yet super powerful to understand how to use these logical operators. You can combine them however you want and in the way that is more practical or makes more sense to you.

Logical Operators and Nested IFs in Power BI / Power Query

What are Logical Operators?