VirajR's Blog
IF Statement using Power Query

IF Statement using Power Query

(Almost) Complete Guide

Viraj Rathnayaka's photo
Viraj Rathnayaka
·

5 min read

Table of contents

If you're stuck with a syntax error and looking for the short answer, here it is.

if  Condition 1  then  results if true else  results if false

if  Condition 1  then  results if true  
        else if  Condition 2  then  results if false  
                    else  results if false

Let's move on to the long answer.

Can I use the IF statement in the Power query?

Yes. you can use an if statement in Power Query but the syntax is a little different from what it is in DAX.

If you've used DAX in Power BI (or Excel ) you know that the expression looks something like this.

IF ( Condition, true result, false result )

See the difference in Power Query.

if Condition 
    then true result 
        else false result

Lowercase if, no commas, no parenthesis, always should end with an else.

How do I write an IF statement in the Power query?

When you open the Power Query editor, you have a few options to get to a point where you can add an if statement.

To demonstrate these options let's start with one instance where we need an IF statement.

In this dataset, we need to add a new column that adds 20% markup value to the sales value of the state "Texas".

Go to the Add Columns Tab. You can use both custom columns or Conditional Columns.

The conditional column offers a more structured and simpler view of the logic we going to right. In the custom column, you are free to be creative. But for this calculation let's go to the Custom column.

Here is the code.

if [State] = "Texas" then  [Sales] * 1.20 else [Sales]

Convert the new column to decimal and the results look like this.

Power Query IF with multiple conditions

Let's say we need to add 20% to Texas 10% to Wisconsin and 1% for everything else. How do we write an IF condition for that?

Multiple conditions if statement in Power Query goes like this.

 = if Condition 1 then  [Value if true] 

else Condition 2 then  Value if [Condition 2 False Condition 2 true] 

else [Value if Condition 1 and 2 False]

conditions can go on and on like this and should be closed by and else at the end.

Here is the code for the state tax conditions above.

= if [State] = "Texas" then  [Sales] * 1.20 

else if [State] = "Wisconsin" then  [Sales] * 1.10 

else [Sales] * 1.01

Here are the results.

Can we do if null or Is null in Power Query?

No. The Isnull function we use in DAX is not directly available in Power Query M. However we can use the IF function with the Null value condition.

Let's see how.

Null in Power query means that that place holds no data. Null is not zero, null is not space. it's just blank. In power query, the null values are presented by the term "null".

Let's say we have a state column with some null values and we need to make the sales value zero for null states.

here is the code for the new custom column.

if [State] = null then 0 else [Sales]

Power Query if statement for a list

Let's say we have a list of 3 values and we need to check if any of these 3 values are contained in a column and do something if true.

To work with multiple values in the IF condition we can use List. Contains () function in M.

List.Contains - PowerQuery M | Microsoft Learn

This function checks the whole column (second argument) against the list ( first argument) and sees if list values are available in the column.

= List.Contains({item 1, item 2, item 3, item 4}, Column Name)

We can couple this with the if function in the Power query and do a calculation on each list-column match.

Let's say we need to add a 20% tax to the states of Illinois, Texas and California.

if List.Contains({"Illinois", "Texas" , "California"}, [State]) 
    then [Sales]*1.20 
        else [Sales]

How to use a list of values to check if the column contains one of them in Power Query

Let's say we have a list of 100 items and we need to write an if statement in Power Query to search a table column for each value and do a calculation if any one of them is available in the column.

(Obviously we don't want to write an if statement for each one 😁 ).

For this example, we use a list of 4 but you can extend it to any number.

First Import the list into Power Query or add that as a table in any way you like.

Here is the Table and Column we need to search in.

This formula will return "Yes" or "No" based on availability.

= if List.Contains(States [State], [State]) 
    then "Yes" 
        else "No"

In the formula, I've used List.Contains( ) function and in the place of the first argument, the States table (single-column table with all states ) has been used as a list.

The best thing about this method is you can extend the list to any number and write an if statement to that.

Conclusion

The IF statement can be used in many ways in Power Query. This includes single conditions, Multiple conditions, Null values, a list of values and a column of a separate table.

PowerQueryPowerBI