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

In this article, we delve into the power of the IF function in Power Query. You’ll learn why mastering this skill helps improve your data analysis capabilities.

In this comprehensive guide, we’ll start from the basics and work our way up to advanced techniques. By the end of this article, you’ll be a Power Query IF statement pro.

Table of contents

• Introduction
  • The IF Function in Power Query
  • Why is the If Statement Important?
• Getting Started with IF Statements
  • Syntax
  • 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 message
  • Token Eof expected
  • Expression.SyntaxError: Token Comma expected
  • Expression.SyntaxError: Token Literal expected
  • Expression.SyntaxError: Token Then/Else expected
• Conclusion

Introduction

Power Query is an amazing tool within Excel and Power BI that helps you connect, clean, and transform data from various sources. The IF statement is a key building block for adding conditional logic to your Power Query transformations.

The IF Function in Power Query

The IF function is essential in your Power Query toolkit. It enables you to compare a value with a specified condition, leading to two possible results. It’s known as a conditional statement.

The function 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.

Why is the If Statement Important?

IF statements are crucial in Power Query because they let you tailor data transformations according to your unique business rules. With the IF statement, you can set up conditional logic that tells Power Query what to do with your data based on specific conditions. This helps you extract valuable insights from your data quickly and efficiently.

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 statement to establish this conditional logic and create a new column with the results.

Eager to learn more? Let’s jump right in and build a strong foundation in IF statement basics.

Getting Started with IF Statements

In this chapter, you’ll learn the fundamentals of IF statements. That includes the syntax, basic examples, and how to create your first IF statement.

Syntax

Before we go any further, let’s understand how to write an IF statement in Power Query. Here’s the basic syntax of a 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.

Remember, Power Query is case-sensitive, so use lowercase for “if”, “then”, and “else”.

Basic Example

Let’s start with a straightforward example to grasp the syntax. Imagine you have a list of numbers and want to label them as “positive value” or “negative value”. You could use an IF statement 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.

Creating Your First IF Statement

Now that you’ve got the basics, let’s make an IF statement together. 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 statement
• Click OK

After pressing the ‘Custom Column’ button in the ‘Add Column’ tab, a new column will be added 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.

Under the hood, Power Query generates this 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 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. With its easy-to-understand syntax, you can quickly and efficiently categorize data or gain valuable insights.

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 enable 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?

Logical Operators

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

Remember to use lowercase for these operators. Let’s look at some examples.

Applying Operators to IF Statements

IF statement with AND Logic

It’s also useful to know how to add if statements with ‘and’ logic to test multiple conditions. Let’s say 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 like this:

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

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"

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"

Do you have a need to incorporate a lot of values? Make sure to check out how to perform the IN operation in Power Query in the upcoming chapter on Advanced Techniques.

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]

These examples create a new column based on logic. If you want to replace values based on conditions, explore replacing values based on conditions. While slightly more complex, it’s closely related to the conditional logic in IF functions.

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

Intermediate IF Statement Examples

This chapter 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, and that’s when nested IF statements come in handy. You can include an IF statement inside another IF statement:

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 like this:

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

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"

Working with Different Data Types

In this chapter, we focus on how to use IF statements 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"

Or test if the combination of two text fields matches another field:

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

You can also return a value whenever a part of a text string matches your test. Imagine that invoices starting with the text “MAR” relate to market revenue:

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

For more inspiration, here’s an article that covers all text functions in Power Query.

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 IF statements 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")

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

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

List.Contains checks whether the “CustomerName” appears in the list of favourite customers. This example only uses three list values. But you can expand this list with as many values as you want. 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.

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.

Power Query uses the ?? construct as Coalesce operator. You can use the coalesce operator in combination with an IF statement to achieve this:

[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]

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 statement 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.

Conclusion

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!