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


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

Leave a Reply

Your email address will not be published.


clear formPost comment