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
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:
- The Table.AddColumn function adds a new column to our table, called “Category”.
- The ‘each’ keyword applies the IF statement to every row in the table.
- The condition checks if the number is greater than 10.
- 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:
Operator | Description |
---|---|
> | Greater than |
>= | Greater than or equal |
< | Less than |
<= | Less than or equal |
= | Equal |
<> | Not equal |
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:
Operator | Description |
---|---|
and | Both conditions must be true |
or | At least one condition must be true |
not | Condition must not be true |
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!
Recommended Reading…
Power Query Foundations
Beginners Guide to Power Query
List Functions (200+ Examples)
Text Functions (150+ Examples)
Creating Tables (40+ Examples)
Generate the Ultimate Date Table
Advanced Topics
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate
Way cool! Some extremely valid points! I appreciate you writing this write-up and
the rest of the site is extremely good.
Ηi there to ɑll, because I am genuinely keen of reɑding this blog’s post to be updated daily.
It contaіns ցood data.
Hi Rick,
I am attempting to add days to a date in a custom coloumn and it is only generating an error.
Please see formula.
if [ship_mode]=(“First Class”) then [ship_date]+3 else “other”
How can I modify this formula to add the days to the ship date?
You can try the following:
More on the Date.AddDays syntax you can find here: https://powerquery.how/date-adddays/
Hi Rick,
I am going insane, PQ will not find the very first line of this code??? All other lines work but not for Food Waste 1????? Why
=if[Round] = “Food Waste 1” and [TonnageGrp] = “FD1Tonnes” then “FD1”
else if[Round] = “Food Waste 2” and [TonnageGrp] = “FD2Tonnes” then “FD2”
else if[Round] = “Food Waste 3” and [TonnageGrp] = “FD3Tonnes” then “FD3”
else if[Round] = “Food Waste 4” and [TonnageGrp] = “FD4Tonnes” then “FD4”
else if[Round] = “Food Waste 5” and [TonnageGrp] = “FD5Tonnes” then “FD5”
else if [Round] = “Garden Waste 1” and [TonnageGrp] = “GD1Tonnes” then “GD1”
else if[Round] = “Garden Waste 2” and [TonnageGrp] = “GD2Tonnes” then “GD2” else “WRONG”
Please help thanks
Hi Rick,
I am a Newby (literally) and was wondering if Power Query Editor can use if statement to process steps.
let
Source,
intRowCount = Table.RowCount(Source),
if intRowCount 0 then
step1,
step2,
Results
else
Results = “No Data”
in
Results
Thanks!
Hi Michael,
Power Query can definitely process logic like that.
You can count the number of rows available in your source (like you do with Table.RowCount).
In a next step you can then create an if statement that references the result of that step (a number).
The first argument of your if statement however now references both step1 and step2 separated by a comma. Did you mean to reference something like:
if intRowCount = 0 then Source else “No Data”
You would be able to return your desired results by referencing the correct stepnames like above.
Can we delete column if a confdition is met only (i.e. if total sum of column1 data = 0) ?
Hi Burak,
I believe it should be possible. You would summarize your table and sum up the values of the value columns. Then filter for columns = 0.
Then use a Table.SelectColumns statement that grabs All column names with Table.ColumnNames, and return the difference of ALL column names, and the column names that have 0 as total.
Hope that helps 🙂
Rick…next time I write a custom column using ‘AND’ instead of ‘and’, please mock me! Thanks for the reminder to use lower case in M code under section 3.6.
Keeping in mind the syntax of all the different language is challenging. You’re not the first and definitely not the last to experience syntax errors in Power Query 😋
Good course and simple to follow. (y)
Thank you
This colours blew my mind.
Hello Rick,
A case where the Token Literal Expected error occurs:
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.
Thanks for sharing Stijn 🙌🏻
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!
Muy completo articulo.
Aprendi cosas nuevas sobre esta funcion, a pesar de que llevo varios años usandola.
Muchas gracias. Excelente
Muchas gracias! 🙂
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!
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.
was my initial thought:
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:
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
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…
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
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”)
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:
Hope that gives you some clues on how to continue.
Regards,
Rick
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
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:
Whereas in Power Query the operators come after the first check:
Hope that helps!
Rick
I am sorry that I cannot participate in the discussion now. Very little information. But I will be happy to follow this topic.
Tried following the above steps and applying the logic to a stock run out date but every entry returns “error”?
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:
To fix this you can wrap the function DateTime.FixedLocalNow() in a Date.From() function. Which results in :
That should fix it.
^Rick
Thank you. The video was really helpful!
Thank you for the kind words!
Thank you, very helpful tutorial.
Great post