Mastering IF Statements in Power Query – Including Nested IF-Statements

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. The M-language conditional statement has two possible results. It first 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 returns another.

In this post, you will learn all about If Statements in Power Query. I will cover its syntax, where to write them, example If formulas and what errors may appear.

1. How to use If Statements

1.1 Syntax

An IF statement is a logical formula. It tests a condition and returns a different value depending on whether the condition is true or false. The syntax of the Power Query If function is as follows:

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

Power Query is case sensitive and the words if…then…else should all be lowercase. Many other programming languages use If Statements, and they often look very similar. Yet the syntax may vary.

1.1. Differences Power Query and Excel

For example, the If formula in Excel looks like:

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

The if function in Power Query differs from Excel in three ways.

2. Conditional Column versus Custom Column

There are two easy ways to add an if-statement. 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

The easiest way to add a conditional statement is by using a Conditional Column. You can go to the Add Column tab in Power Query, and click on Conditional Column. It allows you to create basic if-statements.

After clicking on Condition Column, the Add Conditional Column menu pops up:

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

• New Column Name: Enter the name for your new column
• Column Name: The column to evaluate your if-condition against.
• Operator: selections depend on the data type of the select Column Name.
  • Text: begins with, does not begin with, equals, contains, etc.
  • Numbers: equals, does not equal, is bigger than or equal to, etc.
  • Date: is before, is after, is equal to, does not equal, etc.
• Value: You can enter a value, or select a column or parameter to compare your evaluation against. This value together with the Column Name and Operator makes up a condition.
• Output: If the condition is met, what should it return? This can be a value, column value, or parameter.
• Else: When the condition is false you can specify what to do. Options are similar to the Output field.

2.1.1. Limitations

Creating a conditional column using the User Interface (UI) may work for basic expressions. For more complex expressions however, you soon stumble upon the limitations of the UI. The most important ones:

• adding complex if statements to test conditions that include multiple columns is not possible. Using this method prevents you from creating if-statements involving 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 this post focuses on writing if-statements using 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.

In Custom Column dialog box allows you to:

1. Write a name for your Custom Column
2. Enter a custom formula
3. Double-click fields in your table. This includes to column reference in your formula

The custom column formulas allow for more complexity. Here you can include combinations of hard-coded values, functions, columns, and parameters for both the if-condition and the true and false–expressions.

Now let’s have a look at example if-statements. You can paste below examples directly in the Custom Column formula box.

3. If statement examples

With some basic examples you easily learn how to write conditional if statements in Power BI. Imagine working with the following dataset. It shows the quantity sold of each order with the respective unit price.

3.1. A simple if statement

You want to create a column that shows the number of items sold on each line. To get the right amount you will have to account for the quantities in each of the package sizes. The column Package indicates the Quantity of each unit. It can refer to a single unit (each), two units (pair), or four units (packet).

When you write logic for only the package size each you can manage with:

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 you want the formula to include the pair package? You can expand your if statement to include multiple conditions.

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

The key to making nested if-statements work is to put the second if statement after the first else clause. In this example, the formula is formatted using spacing and separate lines. This improves the readability and still performs correctly.

You will soon get the hang of the if…then…else construct in Power Query. You can then easily combine multiple if functions to include the 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 code examples 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

The conditions used so far test whether column values are equal to a single value. To make your conditions a bit more advanced you can use common operators. Common operators can be:

=      // Equals
<>     // Is not equal to
>      // Bigger than
>=     // Bigger than or equal to
<      // Smaller than
<=     // Smaller than or equal to

You can create multiple if statement using these operators. This 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

In some cases you may want to test whether one of multiple conditions is true by combining if with or. Imagine you want to add a column that specifies whether a line refers to a single product unit or multiple product units. And you are given the following considerations:

• You should base your conditions on the Package column.
• The package column contains three unique values. In the future other package sizes may be introduces. All other packages should be shown as other.
• 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. Just make sure to write the word or in lowercase.

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

3.5. The IN function equivalent

When you 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 you can 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. You can expand this list with as many values as you want though! You can even reference a column with values to check.

Want to learn more about lists? Make sure to check out my complete guide to lists with numerous examples.

Read more: How to use Lists in Power Query – Complete Guide »

3.6. If statement with AND Logic

It’s also useful to know how to add if statements with and logic to test multiple conditions. 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 should include two conditions. You can do that by adding AND logic to your if 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"

The shown examples create a new column based on logic. In case you simply want to replace values based on conditions, make sure to delve into replacing values based on conditions. It’s a bit more complex, but strongly related to the conditional logic in if functions.

4. Error messages

The differences between conditional statements 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

You may get the error Token Eof expected when you mistake your capitalization or if an incorrect function name is used. 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 throws the error. The below example shows the word IF capitalized and you can see the error message: Token Eof expected.

4.2 Expression.SyntaxError: Token Comma expected

Another common error is the Expression.Syntaxerror: Token Comma expected. It can occur 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

Another common error is the Token Literal expected. Token Literal expected means the formula expects a condition, value, column name or function somewhere in the formula but does not receive one.

When adding conditions to your formula that include words like not, and, and or, you may get this 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“.

4.4 Expression.SyntaxError: Token Then/Else expected

Last but not least two other errors can occur in the following situation: Token Then expected and Token Else expected. In Power Query the words then and else separate arguments within the if function.

This means that when writing nested if statements, each of the statements needs to have a then and an else clause. If you omit the word 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 BI. One thing we didn’t cover is creating conditional statements by writing custom M-code using the advanced editor. If you’re up for a challenge make sure to check out how to return values based on a condition. Mastering that skill will strongly improve the amount of data challenges you can tackle. That’s all I want to share about the Power Query/Power BI if statement. See you next time!