The IF Function in Power Query: Tutorial, Example If Statements and Errors Explained (Complete Guide)

The IF function in Power Query is one of the most popular functions. It allows you to make comparisons between a value and what you’re looking for. You use it in conditional statements and it has two results. First, it determines whether a condition is met or not. Then, when the specified condition equals true, Power Query returns one result. And when it’s false it will return another. In this post, you will learn all about if statements. I will cover its syntax, where to write them, example if statements and what errors may appear.

1. Syntax

An IF statement is also referred to as a logical formula. The syntax of a basic IF function in Power Query is as follows:

if "if-condition" then "true-expression" else "false-expression"

All programming languages use IF statements, and they often look very similar. Yet the syntax may vary slightly.

1.1. Differences between Excel and Power Query

For example, the IF function in Excel looks like:

IF( "if-condition" , "true-expression" , "false-expression" )

The if formulas in Excel differ from Power Query in three ways.

2. Conditional Column versus Custom Column

There are two easily accessible ways to add an if-statement. And you can find both in the Add Column tab in the Power Query ribbon. Using the user interface one could either add a Conditional Column or write it from scratch by adding a Custom Column.

2.1. Using a Conditional Column

Without any coding knowledge, the easiest way to add a conditional statement is by using a Conditional Column. You can find the button to create one in the Add Column tab. It allows you to create basic if-statements.

After clicking on Condition Column, the below menu opens:

You can use this menu to set up conditional logic. The different options are:

Beginners may be happy with the conditional column using the User Interface (UI). For more complex expressions, however, you soon stumble upon the limitations of the UI. For example:

• adding complex if statements to test conditions that include multiple columns is not possible. This could involve operators like AND, NOT, and OR.
• evaluations can only be done with the operators provided in the default menu. Others (like Date.Year, Text.Start, Text.Proper, etc.) can’t be performed through the provided menu. You would need to add a helper column to make these comparisons.
• the result of a true or false expression can only be a hard-coded value, column value, or parameter. Returning a calculation that combines these three is not possible in the interface.

To address these limitations one could instead write a Custom Column.

2.2. Using a Custom Column

When you need more complex if-statements you can resort to the Custom Column. To create one you can click the Custom Column button found in the Add Column tab of the ribbon. This will open the custom column dialogue box.

The custom column formulas allow for more complexity. Here one can include combinations of hard-coded values, functions, columns, and parameters for both the if-condition and the true and false–expressions. More on example if-statements you will find below. You can paste these examples directly in the Custom column formula box.

3. If statement examples

You have now seen the theory. But to get the hang of it, I will provide you with several examples on how to write conditional if statements. Let’s imagine we’re looking at the following dataset. It shows the quantity sold of each order with the respective unit price.

3.1. A simple if statement

The column Package indicates the Quantity of each unit. It can refer to a single unit (each), two units (pair), or four units (packet). We can use the if function to create a column to show the number of items sold.

So without the batches. One could write:

if [Package] = "Each" then [Quantity] else null

3.2. Nested if statements

This is great, but it only shows numbers when the package is sold by unit. What if we would want the formula to include pairs, using an if statement with multiple conditions? We will need to write a nested if statement to achieve this.

if [Package] = "Each" then [Quantity]     else 
if [Package] = "Pair" then [Quantity] * 2 else null

The key to making this work is to put the second if statement after the first else clause. In this example, I have used spacing and put the formula on two lines. They help readability and still perform correctly. This should make it even more clear.

One could easily combine multiple if functions to include batches of 4 in there as follows:

if [Package] = "Each"   then [Quantity]       else 
if [Package] = "Pair"   then [Quantity] * 2   else 
if [Package] = "Packet" then [Quantity] * 4   else null 

The result looks like the below picture.

Notice that you can add the before specified code in the Custom Column box in the Add Column ribbon menu. The M-code in the formula bar also includes the relevant syntax for the Table.AddColumn function. Everything that comes after the word each is similar to the if-statement displayed earlier.

3.3. If statement with Common Operators

So far the conditions tested column values to be equal to a single value or a list of values. To make your conditions a bit more advanced you can use common operators. Common operators can be:

Applied to an if-statement, these operators could look like:

if [#"Order No."] >= 10       then "LATEST"       else 
if [Unit Price]   >  200      then "EXPENSIVE"    else 
if [Items]        <> "Single" then "NO SINGLE"    else 
if [Units]        <  6        then "EASY"         else 
if [Units]        <= 10       then "MODERATE"     else null)

3.4. IF statement with OR Logic

Let’s imagine you want to add a column that specifies whether a line refers to a single unit of product or multiple. And you are given the following considerations:

• You should base your conditions on the Package column.
• The current column contains three unique values but in the future, this amount could change. Changes should not be considered.
• Any values that do not equal each, pair, or packet should return null.

To achieve this, you can add OR logic to your if statement. Write the word or in lowercase.

if [Package] = "Each" then "Single" else 
if [Package] = "Packet" or [Package] = "Pair" then "Multiple" 
else null

3.5. The IN function equivalent

When you need to check whether a column contains one of many values, it may be too arduous to add OR logic to your if statements. As an alternative one could provide the values to test as a list. Other programming languages often use the IN function for this. The equivalent of the IN function in Power Query uses List.Contains:

if [Package] = "Each" then "Single" else 
if List.Contains( {"Packet", "Pair"}, [Package] ) then "Multiple" 
else null

The function evaluates whether the list contains the value in the column Package. If the value appears, the expression returns true. This example only uses two values in its list. One could expand this list to as many as required though.

3.6. If statement with AND Logic

Your company gives discounts when you order at least 5 packets for a unit price of at least 200. To test this, your conditional statement needs to include two conditions. You can do that by adding AND logic to your statement, also written in lowercase:

if [Package] = "Packet" and [Unit Price] >= 200 then "Discount" 
else "No Discount"

This example only included a single and operator, but know that you could add more to the same expression.

if [Package] = "Packet" and [Unit Price] >= 200 and 
[Unit Price] < 300 then "Discount" else "No Discount"

3.7 If statement with NOT Logic

You may sometimes find the need to test whether something is not true. The not operator can help you out here. Let’s imagine we want to reverse the previous statement. You can add the word not right after the word if and make sure to put the entire if condition between parentheses.

if not ([Package] = "Packet" and [Unit Price] >= 200) then 
"Discount" else "No Discount"

4. Error messages

The differences between a conditional statement in Power Query and Excel are small but important. Especially since small mistakes easily cause errors in Power Query. And the error messages are often not very helpful.

4.1 Token Eof expected

For example, you should write the words if, then, and else in lowercase for a working formula. If you write any of these letters in uppercase in the Custom Column box, Power Query will throw the error. The below example shows the word IF capitalized and you can see the error message below.

Most users won’t know what “Token Eof expected” means. In this case, the message occurs when you write one of the earlier mentioned words in capital letters in the Custom Column box.

4.2 Expression.SyntaxError: Token Comma expected

A different error occurs when you edit your formula in the formula bar. Taking the same example as before, the capitalized IF word now results in a different error message.

The message “Expression.SyntaxError: Token Comma expected” can be confusing. From the first part, I deduct there is a Syntax Error. The second part interestingly suggests a missing comma is causing the error. And this is not the case here.

4.3 Expression.SyntaxError: Token Literal expected

When adding conditions to your formula that include words like NOT, AND, and OR, you may get another error. In the example below, you can see the word and that suggests another condition is coming. Yet no additional condition is written. The word else follows after and indicates the second argument of the function should begin. For as this an incorrect expression Power Query returns: “Expression.SyntaxError: Token Literal expected“. An error that is not very clarifying to the average user.

4.4 Expression.SyntaxError: Token Then/Else expected

Last but not least two other errors can occur in the following situation. In Power Query you can write if statements with the words then, and else to separate arguments. This means that when writing nested if statements, each of the statements needs to have a then and an else clause. If you omit these and replace them by a separator, you would get one of the following error messages:

Expression.SyntaxError: Token Then expected.

Expression.SyntaxError: Token Else expected.

These last two errors are a bit clearer, but can still confuse users. After all, what is a token?

In this article, I showed several examples of how one could leverage if-statements in Power Query. One thing we didn’t cover is creating conditional statements by writing custom M-code using the advanced editor. This, however, is out of the scope of this article. So that’s all I want to share about the if function in Power Query. See you next time!