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.

IF Function in Power QueryIF Function in Excel
Has a lowercase syntax. Power Query is case sensitive. Capitalizing any letter in the words if, then, and else throws an error.Has an uppercase syntax. Yet Excel automatically converts lowercase to uppercase. And it does not throw an error.
Separates arguments by the lowercase words then and else.Separates arguments by a default separator. Mostly a comma or semicolon.
The words if and else determine the beginning and end of an if function.Arguments are always provided between parentheses. These show the start and end of the function.

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.

Conditional Column button

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

Conditional Column menu

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.

Button to create a Custom Column

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

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.

Dataset for if functions

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.

Nested if statements in Power Query

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.

Token Eof expected error message

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.

Expression.SyntaxError: Token Comma expected 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“.

Token Literal Expected Error Message

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!

About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

20 thoughts on “Mastering IF Statements in Power Query – Including Nested IF-Statements”

  1. Hello Rick,

    A case where the Token Literal Expected error occurs:

    #”Select_Rows” = Table.SelectRows( My_table, each [Language] = (if List.Contains({“Brasil”, “Brazil”,”Brasilia”}, [Country]) then “PT” else “ES”)),

    First I hadn’t wrapped the if function in parenthesis, so Power Query read [Language] = if and stopped, since this statement ends with if, my if function wasn’t finished and sent the Token Literal Expected error.

    Reply
  2. I don抰 even know the way I finished up here, however I assumed this publish was great. I do not realize who you are but definitely you are going to a famous blogger if you are not already 😉 Cheers!

    Reply
  3. Muy completo articulo.
    Aprendi cosas nuevas sobre esta funcion, a pesar de que llevo varios años usandola.
    Muchas gracias. Excelente

    Reply
  4. Hi!

    I’m trying to create a custom column with a formula that looks at 2 columns (due date & completed date). If the due date is before today AND the completed date column is showing “null” then I want the custom column to return “overdue”. I’ve tried a few different things and i’m not able to get the formula right.

    thanks in advance!

    Reply
  5. Hello Rick,
    thanks a lot for the insights, comments and inspirations in your articles!

    I finally solved a use case that I would like to share and maybe ask if there is a better solution.

    The starting point is a table with workitems, basically tasks from a todo list. Each item has an [ID], some have a [ParentID]. Due to limited data history some of the parent items don’t exist anymore in the table. Therefore, I need to find those ‘orphan’ parent IDs and clear them.
    As I stumbled across the chapter 3.5 referring to the equivalent of the ‘in’ function and my target was to create a new column [existingParentID] that contains the value of the Parent ID, given that it is among those work item IDs.
    // List.Contains({[ID]}, [ParentID])
    was my initial thought:

    //Table.AddColumn(Table, “ExistingParentID”, each if List.Contains({[ID]}, [ParentID]) then [ParentID] else null)

    The [ParentID] of each row was the value to be searched for and the whole column [ID] was supposed to be the list to be searched in.
    It turns out that the engine was iterating through each row, pulling out the ID, creates a list from the single value and compared it against the single ParentID value from that row, obviously yielding ‘false’.

    My next target was to use the [ID] column as a fixed list to be searched from. Long story short, I struggled a lot and finally created a new query with a single [IDlist] column from the very same data source that I could use inside my main query:

    //Table.AddColumn(Table1, “ExistingParentID”, each if List.Contains(#”new Query”[IDlist], [ParentID]) then [ParentID] else null)

    This resulted in an almost endless load-time, as the engine used to pull the #”new Query”[IDlist] and searches for the [ParentID] of row one…. Next it pulls again the #”new Query”[IDlist] and searches for [ParentID] of the second row…. And so on.

    The solution was to create a new myListQuery that yields only the IDs in a list and then use
    //buffedList = List.Buffer(myListQuery)
    inside the main query.

    This way the M-engine first loads the myListQuery, buffers it and is able to use the buffedList as a static list from which it can search and check if each ParentID value is actually present among the IDs.

    Summarized:
    1. New list-query: myListQuery
    2. ‘store’ list in memory: //buffedList = List.Buffer(myListQuery)
    3. Create the new column: //Table.AddColumn( table , “ExistingParentID”, each if List.Contains(buffedList, [ParentID]) then [ParentID] else null)

    For me that was a tough cookie to chew, now being a piece of cake 🙂
    I wonder if a simpler / single query solution is possible…

    Reply
    • Hi Marc,

      I can tell you really did your research here. And I’m impressed you started juggling with both Column references and the List.Buffer function. Those really helped in the speed of your query.

      So what I can tell from what you wrote: in each row you have an ID and a parent ID, and you are to check whether that parent ID exists in the query.

      One of the most efficient solution is probably to merge the query with itself.

      You can do that by going to Merge Query, and in the selection pain select the current query name.

      Then Merge the Parent ID of the top table, with the Orphan ID on the bottom table. And do either an
      – inner join to only keep the rows where a parent ID exists in the data set.
      – Or do an anti-join to keep the rows of which the parent id is missing.

      Hope that makes sense!

      Rick

      Reply
  6. I am trying to create a Custom column in Power BI using the below statement. I keep getting the “token comma expected” error after the word all. I have tried all sorts of modifications and nothing has worked. Thoughts? Thank you.

    =for([ca BOOKING_DATA_VW.OFFENDER_BOOK_ID] in all [ca BOOKING_DATA_VW.OFFENDER_BOOK_ID], if “No” [Is New Book Detox Housing] Return “Not Detox” Else: Return “Detox”)

    Reply
    • Hi TaMara,

      Thanks for commenting. Power Query does not use ‘for’ and ‘return’. Please have a look at the syntax I described in the article.

      Furthermore, I don’t follow your requirements. Are you looking to:

      if List.Contains( StepName[ca BOOKING_DATA_VW.OFFENDER_BOOK_ID], [ca BOOKING_DATA_VW.OFFENDER_BOOK_ID] ) then “Result1” else “Result2”.

      Hope that gives you some clues on how to continue.

      Regards,
      Rick

      Reply
  7. Hello, thanks for the tutorial. I have a silly problem tough: I can´t get PowerQuery to recognize as a formula the “and” and “or” operators. They dont turn blue like if, then and else, and therefore dont work. Any idea why?
    thanks

    Reply
    • Hey Manual,

      Can you drop the code you are using? Could it be you’ve placed the ‘or’ and ‘and’ operators at the start perhaps?

      In Excel one can do:
      IF( AND( a = 6, b = 10), “true”, “false” )
      IF( OR ( a = 6, b = 10), “true”, “false” )

      Whereas in Power Query the operators come after the first check:
      if a = 6 and b = 10 then “true” else “false”
      if a = 6 or b = 10 then “true” else “false”

      Hope that helps!
      Rick

      Reply
  8. I am sorry that I cannot participate in the discussion now. Very little information. But I will be happy to follow this topic.

    Reply
  9. Tried following the above steps and applying the logic to a stock run out date but every entry returns “error”?

    if Date.AddDays([RunoutDate],-14)<DateTime.FixedLocalNow()
    then "Raise Job ASAP"
    else Date.AddDays([RunoutDate],-14)

    Reply
    • Hello James,

      The error is correct. Do you know how to inspect the error? When you click in the cell where the error is (don’t click the word error, but next to it), the error message appears. It will tell you that:

      Expression.Error: We cannot apply operator < to types DateTime and Date.

      To fix this you can wrap the function DateTime.FixedLocalNow() in a Date.From() function. Which results in :

      Date.From( DateTime.FixedLocalNow() )

      That should fix it.
      ^Rick

      Reply

Leave a comment