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:
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.
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
Hey! This is an article for power query and not really for dax. Sorry
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?
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
Hey!
I don’t think that the article shown above would help for this scenario as you’ve mentioned that you’re after a merge and not just a simple logical operator.
I’d recommend checking out these articles that I wrote on the official Microsoft Power Query documentation on the Merge operations:
https://docs.microsoft.com/power-query/merge-queries-overview
You can also ask questions using your own dataset on the official Power Query forum here:
https://docs.microsoft.com/en-us/answers/topics/power-query-desktop.html
Best!
-Miguel