IF Statements in Power Query M (Incl Nested IFs, OR, AND)

In this article, we delve into the capabilities of the IF function in Power Query. You’ll learn why conditional statements are some of the most important concepts in the M language, and how you can use them in your queries. We’ll start from a basic if-then-else clause and work our way up to advanced techniques.

Table of contents

• The IF Function in Power Query
• Getting Started with IF Statements
  • Basic Example
  • Creating Your First IF Statement
• Conditional Logic in Power Query
  • Comparison Operators
  • Logical Operators
• Applying Operators to IF Statements
  • IF statement with AND Logic
  • IF statement with OR Logic
  • IF statement with NOT Logic
• Intermediate IF Statement Examples
  • Nested IF Statements
  • IF Statement with Multiple Conditions
  • Using the IF Statement to Categorize Data
• Working with Different Data Types
  • Working with Text
  • Working with Dates
• Advanced IF Statement Techniques
  • In Operator Equivalent
  • Coalesce Operator
  • Type Compatibility Operator
• Error messages
  • Token Eof expected
  • Expression.SyntaxError: Token Comma expected
  • Expression.SyntaxError: Token Literal expected
  • Expression.SyntaxError: Token Then/Else expected
• Conclusion

The IF Function in Power Query

The IF statement is an important building block for adding conditional logic to your Power Query transformations. It allows you to test a value with a specified condition, leading to two possible results (also known as a conditional statement). The if formula first checks if a condition is met. If the condition is true, Power Query returns one result. If it’s false, it returns another result.

For instance, imagine you have a table of sales data and want to create a new column. This column should indicate whether each sale was high or low value. You could use an IF condition to establish this conditional logic and create a new column with the results. Let’s see how this works.

Getting Started with IF Statements

Let’s look at the fundamentals of the IF statement in the M language. That includes the syntax, basic examples, and how to create your first IF condition.

Here’s the basic syntax of the Power Query IF statement:

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

This can be broken down into three parts:

• condition: This is the condition you’re testing, such as comparing a value to a specific number, or combining multiple conditions using logical operators.
• value-if-true: This is the result or action you want when the condition is true.
• value-if-false: This is the result or action you want when the condition is false.

SQL users will see that the power query if statement has strong similarities to the case-when statement in SQL.

Basic Example

Let’s continue with a straightforward example to grasp the syntax. Suppose you have a list of numbers and want to label them as “positive value” or “negative value”. You could use an IF formula like this:

if [Value] > 0 then "Positive value" else "Non-positive value"

In this example, the condition checks if the value is greater than 0. If it is, the “Positive value” is returned. If not, the “Non-positive value” is returned. Now that you’ve got the basic syntax, let’s make an IF statement together.

Creating Your First IF Statement

Say you have a list of numbers and want to label them as “high” or “low”. You could use an IF statement like this:

if [Price] > 10 then "High Price" else "Low Price"

Here’s how to create your IF statement:

• Go to the Add Column tab in the ribbon
• Select Custom Column
• Provide a Column Name
• Enter your IF condition as Custom Formula
• Click OK

After pressing the ‘Custom Column’ button in the ‘Add Column’ tab, Power Query adds a new column to your dataset.

The ‘Custom Column’ pop-up will appear, where you can provide both a Column Name and a Column Formula. Be sure to write “if”, “then”, and “else” in lowercase, and click ‘OK’.

Voilà! You now have a table with a newly created column.

After applying the above steps, Power Query generates the following code:

Table.AddColumn(
    #"Changed Type", 
    "Category", 
    each if [Price] > 10 then "High Price" else "Low Price" )

Here’s a quick breakdown:

1. The Table.AddColumn function adds a new column to our table, called “Category.
2. The ‘each’ keyword is shorthand for a function that applies the IF statement to every row in the table.
3. The condition checks if the number is greater than 10.
4. If the condition is true, “High Price” is returned. If false, “Low Price” is returned.

As you can see, the IF statement’s conditional logic helps Power Query classify data based on specific conditions.

Conditional Logic in Power Query

With the basics in hand, it’s time to delve deeper into conditional logic in Power Query. Understanding conditions, comparison operators, and logical operators will allow you to create more powerful IF statements.

Comparison Operators

Comparison operators let you compare values within your conditions. Here are the most common operators in Power Query:

For instance, to check if a product’s revenue equals $500, use the equal operator like this:

if [Revenue] = 500 then ... else ...

To classify ages 18 or younger as “Youth” and the rest as “Other“:

if [Age] <= 18 then "Youth" else "Other"

You get the idea. Now, what if you want to combine multiple conditions?

Using Operators with IF Statements

Logical operators allow you to combine multiple conditions. The main logical operators are:

Let’s look at some examples that show how you can apply the different operators.

Applying Operators to IF Statements

There are three logical operators in the M language, and here’s how they work for and statements, or statements and not statements.

IF statement with AND Logic

It’s useful to know how to add if statements with ‘and’ logic to test multiple conditions. Suppose you want to find products with revenue greater than $500 and less than $900. To test this, your conditional if statement should include two conditions. You’d use the and operator as follows:

if [Revenue] > 500 and [Revenue] < 900 then "Relevant Products" else "Other"

The resulting column categorizes each product. While this example only included a single and operator, you can add more conditions to the same expression:

if [Revenue] > 500 and [Revenue] < 900 and [Category] = "T-Shirts" 
   then "Relevant Products" 
   else "Other"

In this example we checked whether the values are bigger than 500 and smaller than 900. Only in those cases they are categorized as ‘Relevant Products’.

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 are looking for the top 2 selling clothing categories. In this case T-shirt or Sweater. You could use the or operator in this way:

if [Category] = "T-Shirt" or [Category] = "Sweater" then "Best Seller" else "Other"

The resulting table categorizes both T-Shirt and Sweater values as ‘Best Seller’.

IF statement with NOT Logic

Sometimes, you need to test if something is not true, either to exclude a condition or because it’s shorter to write the negative form. For instance, let’s say you want to increase the price of everything except lemons by 10%

You can add the not operator right after the word if. Just make sure to put the entire condition between parentheses.

if not ( [Food] = "Lemon" )  then [Price] * 1.1 else [Price]

In this scenario, all products prices except for lemons are multiplied by 1.1.

With a solid understanding of conditional logic, let’s move on to intermediate IF statement examples.

Intermediate IF Statement Examples

This section provides examples of how to work with nested IF statements, multiple conditions, and using the IF statement to categorize data effectively.

Nested IF Statements

Sometimes, you need to check multiple conditions in sequence. That’s when nested IF statements come in handy. Nested IF statements ‘glue’ multiple if conditions together. You can include an IF statement inside another IF statement as follows:

if [Condition1] then [Value1] else if [Condition2] then [Value2] else [Value3]

Without formatting, any code is difficult to read. So from now on, I will serve you formatted code. The results are identical, they are simply much easier to read. Here’s the same code but formatted:

if [Condition1]     
   then [Value1] 
   else if [Condition2] 
           then [Value2]  
           else [Value3]

Let’s say you want to categorize products by revenue as “Low”, “Medium”, or “High”. You could use nested IF statements as follows:

if [Revenue] < 500 
   then "Low"
   else if [Revenue] < 1000 
        then "Medium"
        else "High"

This effectively allows you to specify three categories of revenue.

IF Statement with Multiple Conditions

You can also use logical operators to create more complex conditions. For instance, when you want to find products with revenue between $500 and $1000 and more than 50 units sold. You’d use both and and or operators like this:

if [Revenue] > 500 and [Revenue] < 1000 and [UnitsSold] > 50 
   then "Match" 
   else "No Match"

Using the IF Statement to Categorize Data

IF statements are great for categorizing data. Let’s say you have a table with student grades, and you want to add a column that shows the grade category (A, B, C, D, or F):

if [Grade] >= 90 then "A"
   else if [Grade] >= 80 then "B"
   else if [Grade] >= 70 then "C"
   else if [Grade] >= 60 then "D"
   else "F"

The code works through each argument until it finds the relevant category. And if none of the conditions match, it returns the Grade Category ‘F’.

Working with Different Data Types

In this following section, we focus on how to use IF conditions with various data types, such as text and dates, to expand your data manipulation toolkit.

Working with Text

Continuing with our IF statement journey, let’s explore how to work with text values. For instance, to check if a product name contains a specific keyword and categorize it you can use Text.Contains:

if Text.Contains( [Product Name], "Widget")  // Does [ProductName] contain "Widget
   then "Widget"   // if yes, return "Widget"
   else "Other"    // else return "Other"

Instead of an exact match, the code allows you to check for a partial match.

You can also test if the combination of two text fields matches another field:

if [FirstName] & [LastName] = [FullName] then true else false

For more specific requirement, you can also resort to other functions. For instance, to return a value whenever a part of a text string matches your test. Imagine that your dataset contains invoices, and whenever they start with the text “MAR” they should categorize as market revenue. You can do that as follows:

if Text.Start( [InvoiceID], 3 ) = "MAR" then "Marketing Revenue" else "Other"

Working with Dates

Dates are another common data type you’ll work with in Power Query. You can use IF statements to manipulate and categorize dates. For example, you can check if a date falls within a specific range:

if [OrderDate] >= #date(2023, 1, 1) and [OrderDate] <= #date(2023, 12, 31) 
   then "2023 Order"
   else "Other Year"

Or, you could categorize dates by day of the week using Date.DayOfWeek:

if Date.DayOfWeek([OrderDate]) = 0         then "Sunday"
   else if Date.DayOfWeek([OrderDate]) = 6 then "Saturday"
   else "Weekday"

Advanced IF Statement Techniques

In this section, we’ll explore more advanced techniques for working with the IF statement in Power Query. We’ll look at the “in” operator equivalent, the coalesce function, and the type compatibility operator.

In Operator Equivalent

Many programming languages have the in operator. The in-operator allows you to check if a value exists in a list. It’s syntax sugar for a set of or-statements. This can be helpful when you want to categorize data based on a predefined set of values.

For example, let’s say you have a list of favourite customers, and you want to add a column to your sales data that shows if a customer is your favourite or not. Power Query does not have the in operator, but you can replicate the in operator by using List.Contains:

if List.Contains(  {"Alice", "Bob", "Charlie"}, [Customer Name] ) 
   then "Favorite" 
   else "Regular")

This is identical to:

if [CustomerName] = "Alice" or [CustomerName] = "Bob" or [CustomerName] = "Charlie"
   then "Favorite" 
   else "Regular"

The above code returns ‘Favorite’ when the Customer Name is one of Alice, Bob or Charlie.

List.Contains checks whether the “CustomerName” appears in the list of favorite customers.

And if you want to learn more about lists, make sure to check out my complete guide to lists with numerous examples.

Coalesce Operator

The coalesce function returns the first non-null value from a list of expressions. This is useful when you have multiple columns with potentially missing data and want to return the first available value.

For example, let’s say you have a table with three columns: “PrimaryPhone”, “SecondaryPhone”, and “TertiaryPhone”. You want to add a new column that displays the first available phone number for each record.

To use the coalesce operator in combination with an IF statement you can write:

[PrimaryPhone] ?? [SecondaryPhone] ?? [TertiaryPhone]??"No Phone"

/* ------------------
-- is identical to
------------------ */

if [PrimaryPhone] = null then
   if [SecondaryPhone] = null then
      if [TertiaryPhone] = null then 
         "No Phone" 
         else [TertiaryPhone]
      else [SecondaryPhone]
   else [PrimaryPhone]

The ‘Phone’ column now returns the first phone number it finds.

Type Compatibility Operator

Sometimes, columns with mixed data types can cause errors in your IF statements. To handle these situations, use the type compatibility operator to check if a value is of a specific type.

For example, let’s say you have a column called “Data” that contains different data types. You want to create a new column that categorizes the data as “Numeric”, “Text”, “Date” or “Other”:

if [MixedData] is number then "Numeric" 
   else if [MixedData] is text then "Text" 
   else if [MixedData] is date then "Date" 
   else "Other"

In this example, we use the is operator to check if the value in the “Data” column is of number, text or date type. This way, we can handle mixed data types without causing errors.

Now you’re equipped with advanced IF condition techniques in Power Query. Next up, we’ll be looking at error messages you might bump into.

Error messages

Power Query and Excel have small but important differences in their conditional statements. Small mistakes can easily cause errors in Power Query, and the error messages are often not very helpful. Let’s explore some common error messages and their causes.

Token Eof expected

You may get the error Token Eof expected when you mistake your capitalization or when using an incorrect function name. 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.

Expression.SyntaxError: Token Comma expected

This error can occur when editing your formula in the formula bar. Interestingly, the error message suggests a missing comma is causing the error, which may not be the case. For example, using a capitalized “IF” can result in this error message.

Expression.SyntaxError: Token Literal expected

Another common error is the Token Literal expected. This error means the formula expects a condition, value, column name, or function somewhere in the formula but doesn’t 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.

As this expression is incorrect, Power Query returns: “Expression.SyntaxError: Token Literal expected“.

Expression.SyntaxError: Token Then/Else expected

These errors can occur when the words “then” and “else” are missing or misplaced within the IF function. In Power Query, “then” and “else” separate arguments within the IF function. When writing nested IF statements, each statement needs to have a “then” and an “else” clause. If you omit the word and replace it with a separator, you’ll get one of these error messages:

Expression.SyntaxError: Token Then expected.

Expression.SyntaxError: Token Else expected.

These last two errors are a bit clearer, but the term “token” can still confuse users. In this context, a “token” refers to a specific word or symbol used in the Power Query language, such as “then” or “else.” When the error message mentions a missing or expected token, it means that a particular word or symbol is missing or misplaced in your formula.

To learn more about error messages, you can refer to this article.

Conclusion

In this article, we delved into several examples that illustrate how you can leverage if-statements in Power Query. It’s an important technique that any developer should master. Knowing how to combine different operators and conditions allows you to categorize your data or build custom logic based on your business rules.

One thing we didn’t cover is replacing values conditionally 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 increase the number of data challenges you can tackle.

Happy querying!