Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

In this article, we delve into the capabilities of the IF function in Power Query. You’ll learn why it’s one of the most important concepts in the M language, and how it helps improve your 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
Mastering the IF Function in Power Query - including Nested-IF statements (Complete Guide)

The IF Function in Power Query

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

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

Important

IF statements are important 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 add valuable information or custom logic to your data quickly and effectively.

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. Let’s jump right in and build a strong foundation in IF statement basics.

Getting Started with IF Statements

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

Let’s first understand how to write an IF statement. 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.

Tip

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. Suppose 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. 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 statement as Custom Formula
  • Click OK

After pressing the ‘Custom Column’ button in the ‘Add Column’ tab, a new column will be added to your dataset.

Create Custom Column in Power Query

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

Basic Example IF statement in Power Query

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

Note

When creating a column, by default the column type is not set. You can either specify it as the fourth argument of Table.AddColumn or perform a separate Change Type operation.

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 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 create categories or get a better understanding of your data.

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:

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

Using Operators with IF Statements

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

OperatorDescription
andBoth conditions must be true
orAt least one condition must be true
notCondition must not be true

Tip

Remember to write these operators in lowercase when incorporating them in your code. Using capital letters results in an error.

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

Applying Operators to IF Statements

Now you’ve seen the most important operators used with if-statements, let’s see how we can use them on some practical examples.

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"
If Statement with And Logic in Power Query

The resulting column clearly 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"
If Statement with multiple And Logic

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"
If Statement with Or Logic

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

Tip

Want to test whether a column contains one of multiple values? Make sure to check out how to perform the IN operation in Power Query later this chapter when dealing with 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]

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

Tip

This article shows how to create a new column using if statements. If instead you need to change values in an existing column, see this article on replacing values based on conditions. This is a slightly more complex but also uses the “if-then” rules.

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

Nested if statements in Power Query

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

Categorizing data with if statement in power query

Working with Different Data Types

In this following section, 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"

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

Using text functions in an if statement

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"

Tip

For more inspiration on the available text functions in the M language, here’s an article that covers most 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"

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

In operator in if statements in Power Query

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

Tip

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

Tip

The coalesce operator in Power Query is represented by ‘??‘. It allows you to return an alternative value in case of null. You can use it by positioning it between two values, the primary value (left) and the fallback value (right). When the primary value on the left is null, the coalesce operator returns the fallback value on the right.

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.

Coalesce operator for if statements in Power Query

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"
Determine Types using if statements in Power Query

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.

Token Eof expected error message

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 Comma expected 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“.

Token Literal Expected Error Message

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!

Share on:

Latest from my blog

  1. I am trying to create a column that shows how long individuals have been employed in years. There are 2 columns: Start Date, Departure Date. I can calculate how long someone who has departed was employed, but I want to also include those who do not have a departure date.

    This formula is creating an error:
    =if([Departure Date]>0,(DateTime.Date( DateTime.LocalNow() )-[Departure Date]),(Duration.Days([Departure Date]-[Start Date]))/365.25)

    Reply
  2. Hi, i created a formula to replace a value in an existing column without creating a new one:

    Table.ReplaceValue(#"Changed Type",null,
      each if [Staffing Status]="Future Open" then "Filled" 
      else each if [Staffing Status]="In Recruiting" then "Open"
      else each if [Staffing Status]= "Filled" then "Filled"
      else each if [Staffing Status]="Open" then "Open"
      else each if [Staffing Status]= "Filled Future Open" then "Filled"
      else each if [Staffing Status]= "Filled in Recruiting" then "Filled"
      else each if [Staffing Status]= "Filled Pending Requisition Approval" then "Filled"
      else each if [Staffing Status]= "Future Open" then "Filled"
      else each if [Staffing Status]= "In Recruiting Future Filled" then "Open"
      else each if [Staffing Status]= "Open Deferred" then "Deferred" 
      else each if [Staffing Status]= "Open Pending JREQ" then "Open" else "",
      Replacer.ReplaceValue,{"Filled/Open"})

    which means that when the column contains a null, it will change it according to a value in another column. in the power query it works perfect, but going to the power pivot all the values change to [Formula]. when i try to change the data type as a step after the replace values, I get an error in all of those who were changed according to the formula.

    Thanks

    Reply
  3. Way cool! Some extremely valid points! I appreciate you writing this write-up and
    the rest of the site is extremely good.

    Reply
  4. 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?

    Reply
  5. 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

    Reply
  6. 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!

    Reply
    • 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.

      Reply
    • 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 🙂

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

    Reply
    • 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 😋

      Reply
  8. 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
  9. 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
  10. Muy completo articulo.
    Aprendi cosas nuevas sobre esta funcion, a pesar de que llevo varios años usandola.
    Muchas gracias. Excelente

    Reply
  11. 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
  12. 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
  13. 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
  14. 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
  15. I am sorry that I cannot participate in the discussion now. Very little information. But I will be happy to follow this topic.

    Reply
  16. 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.