Conditions in Power Query M language

This is about the basic logic of conditions in Power Query M. 

For conditions there is very useful button “Conditional column” – but it works only for one condition or for multiple conditions going one by one. If you need logical operators or nesting of conditions, you have to write them “manually”.

How to do it?

Let´s use this table, containing information about cars.

Basic conditions

The first condition recognizes, whether the car is Porsche or not. In a custom column it looks like this:

• =if [Brand] = “Porsche“ 
     then “This is Porschee“ 
     else “This is not Porsche“

and the whole step in M looks like this:

• = Table.AddColumn(#”Changed Type”, “Is it Porsche?”, each if [Brand] = “Porsche” then “This is Porsche” else “This is not Porsche”)

More conditions, one by one

This condition recognizes Fords, Porsches, Fiats and another brands. If multiple conditions are true, then only the first one is accepted.

In a Custom column it looks like this.

• =if [Brand] = “Porsche” then “This is Porsche” 
  else if [Brand] = “Ford” then “This is Ford” 
  else if [Brand] = “Fiat” then “This is Fiat” 
  else “Other brand”

Two conditions that have to be true at the same time

You can use the AND operator. This condition recognizes, whether it is a silver Porsche or not.

• =if [Brand] = “Porsche” and [Color]= “Silver” 
  then “This is a silver Porsche” 
  else “This is not a silver Porsche”

Two conditions, when at least one is true

The OR operator works analogically to AND. This condition recognizes cars, that are either silver, or Porsche, or silver Porsche.

• =if [Brand] = “Porsche” or [Color]= “Silver” 
  then “This is any Porsche, any silver car or silver Porsche” 
  else “This car is neither Porsche, nor silver”

The AND and OR combination

How it works when you need blue Fords and silver Porsches?

• =if [Brand]=”Porsche” and [Color]=”Silver“ 
  or [Brand]=”Ford” and [Color]=”Blue“ 
  then “Silver Porsche or blue Ford“ 
  else “Neither silver Porsche, nor blue Ford“

Brackets

But what if you need to find cars, that are Fords or Porsches, but silver only?

If you tried this, it wouldn’t work. It would find all Porsches, regardless of color, and silver Fords.

• =if [Brand]=”Porsche” or [Brand]=”Ford“ and [Color]=”Silver“ 
  then “Silver Porsche or Silver Ford“ 
  else “Something else“

We need brackets to set the priorities. So first of all we´ll find, whether it is one of the brands we need, and then the color is checked. 

• =if ([Brand]=”Porsche“ or [Brand]=”Ford“) and [Color]=”Silver“ 
  then “Silver Porsche or Silver Ford“ 
  else “Something else“

Condition in condition

Let´s come back to an example when we were looking for silver Porsches, but now we don´t want to use AND. 

This example can be handled using nested conditions (brackets are not necessary here):

• =if [Color]=“Silver” 
  then (if [Brand]=“Porsche” 
     then “Silver Porsche” 
     else “Is not silver Porsche”) 
  else “Is not silver Porsche“