The IF expression in Power Query is one of the most popular functions. It selects from two expressions based on the value of a logical input value and evaluates only the selected expression. First, it determines whether a condition is met or not. Then, when the specified if-condition equals true, Power Query returns and evaluates the true-expression , and when it\’s false it will evaluate and return false- expression .

Basic Conditions

if [if-condition] then [true-expression] else [false-expression]

• The first condition identifies if the car brand is Jeep or not. In a custom column form it will look like this:

       = if [Car Brand] = „Jeep“

       then „Yes“

       else „No“

       

• While the whole step in M ​​looks like this in Advanced Editor:

Table.AddColumn (# „Changed Type“, „isJeep“, each if [Car Brand] = „Jeep“ then „Yes“ else „No“)

Multiple Conditions

if [if-condition] then [true-expression] else if [if-condition] then [true-expression] else [false-expression]

• Let\’s say we would like to return the currency for specific countries: Switzerland, Philippines, and United States. For the rest of the countries, we would like to return „N / A“. We can use \’else if\‘ to add a condition for each country.  

     = if [Country Sold] = „Switzerland“ then „CHF“

     else if [Country Sold] = „Philippines“ then „PHP“

     else if [Country Sold] = „United States“ then „USD“

     else „N / A“

Nested Conditions

if [if-condition] then ([if-condition] then [true-expression] else [false-expression]) else [false-expression]

• This is one option when using if expressions with multiple criteria. Round brackets or parentheses are not necessary here.
• We will use the following example:

       First, we would like to determine only the prices of those under Car brand Jeep then we check if its currency is in CHF to calculate the prices accordingly.

       = if [Car Brand] = „Jeep“

       then (if [Currency] = „CHF“

       then [Price] * 2

       else [Price] * 0.5)

       else „N / A“)

Using AND operator

if [if-condition1] and [if-condition2] then [true-expression] else [false-expression]

• This is used when you need to include two conditions in your statement.
• For this example, we would like to return “Discounted” for all the car brand “Ford” in Europe. Otherwise, we will return „Not Discounted“

      = if [Continents] = „Europe“ and [Car Brand] = „Ford“

      then „Discounted“ else „Not Discounted“

Using OR operator

if [if-condition1] or [if-condition2] then [true-expression] else [false-expression]

• The OR operator works analogically to AND. This condition recognizes either of the conditions.
• We would like to return the value 1 to either of the car brands: Ford, Jeep, or Toyota. And return 0 value to other car brands.

      = if [Car Brand] = „Ford“ or [Car Brand] = „Jeep“ or [Car Brand] = „Toyota“ then 1 else 0

If-conditions with common operators

To make your if-conditions a bit more advanced you can use common operators. Common operators can be:

= Equals

<> Is not equal to

> Greater than

> = Greater than or equal to

<Less than

<= Less than or equal to

Applied to an if-statement, these operators could look like:

= if [Car Model Year]> = 2018 then „LATEST“ else

if [Price]> 1000000 then „EXPENSIVE“ else

if [Price] <> 2000 then „NOT PROMO“ else

if [Date Purchased] <“01/01/2000” then „OLD RECORD“ else

if [Date Purchased] <= “01/01/2015” then „UNTIL JAN.2015“ else null

Error messages

Small mistakes can easily cause errors in Power Query, and the error messages are often not very helpful. The three main places where we can edit the M code: Custom Columns, Advanced Editor, Formula Bar. M code errors can be challenging to find, especially if you\’re new to the language. A comma or mistyped word is enough to cause the process to fail. We will not cover all possible errors in Power Query but only those which are common when using M code If expressions . Below are some of it:

Token Eof expected

Write the words if, then, and else in lower case for a working formula. If you write any of these letters in uppercase in the Custom Column box, Power Query will throw the error.

Expression.SyntaxError: Token Comma expected

A different error occurs when you edit your formula in the formula bar. Taking the same example as before, the If word with uppercase letter I now results in a different error message.

Token literal expected

When adding conditions to your formula that include words like NOT, AND, and OR, you may get an error if you use operator AND that implies another condition will be added, yet no additional condition is written. See example below. For as this an incorrect expression, Power Query returns: „Token Literal expected“. An error that is not very clarifying to the average user.

Token Then / Else expected

These two errors can occur in the following situation: In Power Query you write if statements with the word then , and else to separate arguments. This means that when writing nested if statements, each of the statements needs to have a then and an else clause. If you omit these or replace them with a separator, you would get one of the following errors.

Learn more about Power Query by browsing other articles regarding Power Query on this website.

Benötigen Sie einen VBA Programmierer?

Wir als exact construct programmieren mit einem Team von rd. 20 Mitarbeitern seit über 10 Jahren Excel-Tools. Wir sind ein Nischenanbieter der spezialisiert auf Makros/VBA-Codes ist. Daneben unterstützen wir auch als 3rd Level Support die IT-Abteilungen rund um Probleme bei MS Office (Excel, Word, PowerPoint, etc.).

Haben Sie ein Excel-Problem? Benötigen Sie einen Makro-Programmierer? Rufen Sie uns unverbindlich an +41 52 511 05 25 oder kontaktieren Sie uns via Kontaktformular