Miguel Escobar Published June 9, 2019

Logical Operators and Nested IFs in Power BI / Power Query

Power BIPower 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:

  • AND
  • OR
  • NOT

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:

image

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:

image

[Number] > 8 and [Number] < 25

and the result of that will look like this:

image

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:

image

[Number]< 15 or [Number] > 25

and the result of that will look like this:

image

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:

image

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:

image

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:

image

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:

image

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

image

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:

image

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.

Be sure to check the next post in this series where I’ll cover error handling or the pseudo IFERROR function in Power Query
Power BIPower Query
Subscribe
Notify of
guest
5 Comentario
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jatin

Hi,
I need DAX formula for power BI as per below criteria for the table.
ID Product Region Period Frequency
1 Soap Asia 2020-03-31 Monthly
2 Dettol EMEA 2020-03-31 Monthly
1 Soap EMEA 2020-02-29 Monthly
3 Powder Asia 2020-02-29 Monthly
4 Bag EMEA 2020-03-31 Monthly
4 Bar EMEA 2020-02-29 Monthly

On the basis of above table, need a formula which will give below results:
ID 1 has moved from EMEA to Asia in March
ID 2 is the new product in March
ID 3 is the closed product in March
ID 4 product has changed in March

Please help me with DAX formula for power BI

Georgie

Hi,

Thanks for this article, it really got me going on Power Query in Power BI.

I’m trying to band time e.g 01:50 would fall into 01:00 – 02:00, how would you write this in Power Query using a Time column as your column reference?

Robert

Would I be able to use something like this to match select text in columns for a Merge?
I have one table with data like:
W C_01
X C_02
Y C_03
Z C_04

I want to match it with data in another table that can have multiple entries in a row, such as:
C_01, C_03 a
C_02, C_03 b
C_02 c
C_03, C_04 d

And I want to Merge the tables to read something like:
W C_01 a
Y C_03 a
X C_02 b
Y C_03 b
X C_02 c
Y C_03 d
Z C_04 d

But the Merge function reads C_01, C_03 as “C_01, C_03” not as both “C_01” & “C_03”

Thank you