• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Power Query / IF Function in Power Query Including Nested IFS

IF Function in Power Query Including Nested IFS

IF functions are some of the most popular formulas in Excel, and in this blog post we will look at how to write an IF function in Power Query.

There are some key differences between IF function in Power Query and their worksheet equivalent, but you can pick them up quickly.

In these examples we will use the sample data below, already loaded into the Power Query Editor.

Sample data to write if function in power query

We have some salespeople, their sales figure and a status that they have attained.

Writing an IF Function in Power Query

In our first example, we will pay our salespeople a bonus of 15% of their sales, only if they are of an Executive status. They will get 8% of their sales if not “Executive”

We will to add a new column to the table, so click the Add Column tab of the Ribbon.

In Power Query there is a Conditional Column button which presents a window to make writing conditional statements user friendly. Good for beginners.

However this is limited and I feel you are much better served by learning to write If statements purely. So we will click the Custom Column button.

Adding a Custom Column in Power Query

The Custom Column window appears. Firstly we can enter a name for the column – for example Bonus.

Creating a custom column

We will then write the If statement in the Custom column formula box provided.

Underneath the box is a message informing you of any errors in your syntax. And to the right, a list of the columns from our table. We can use this list to enter the columns into our formula instead of typing them (and potentially making silly mistakes, so I’m a fan).

We will enter the following formula.

= if [Status] = "Executive" then [Sales] * 0.15 else [Sales] * 0.08
First If statement in Power Query example

There are a few things you need to know when writing If statements in Power Query.

  • The formula is written in lower case.
  • It is case sensitive and there is a difference between “If” and “if”.
  • There are no commas. Instead the words “then” and “else” are used to separate the test, the value if true and value if false (this will be familiar to VBA users).
  • Spaces are typically entered between the words to make it more readable.

Notice there are no errors detected in our formula.

With the new column added and still selected, you can see the M code (name of the language) in the Formula bar. And you can edit it up here in the future.

It is also added as an applied step on the right and can easily be removed also.

New column and formula added

So that is our first If statement example. Let’s do another one.

Using And and Or in your If Statements

You may have used the AND and OR functions in your IF formulas before in Excel to create more complex logic. This can be difficult when users are just beginning with formulas.

Well the good news is that it is actually easier in Power Query.

For this example, we will pay our salespeople a bonus of 15% is their status is “Executive” or “Gold”, otherwise they get 8% of their sales.

We can use the following formula.

= if [Status] = "Executive" or [Status] = "Gold" then [Sales] * 0.15 else [Sales] * 0.08
Example of If statement with Or in Power Query

All we need to do was enter the word “or” between the two conditions. Wonderfully easy.

The same would apply with the And logic. Simply enter the word “and” between the conditions.

It is also possibly to have more than two conditions.

Writing Nested IF Functions in Power Query

For one last example, we will look at write a nested If function in Power Query. Once again this is nothing to fear, and is actually quite simple.

For this example we will pay a different bonus depending on whether they are of “Executive”, “Gold” or “Silver” status.

The key differences again when writing nested ifs in Power Query is that it must be in lower case, and also there will be no commas or brackets.

We can use the following formula.

= if [Status] = "Executive" then [Sales] * 0.15 else

if [Status] = "Gold" then [Sales] * 0.1 else [Sales] * 0.05
Nested if functions in Power Query

It was not necessary to write this on two lines, but it does helping reading nested ifs.

So If functions are a little different in Power Query than in worksheet formulas, but they are simple to learn and a nice step into the world of writing M code in Power Query.

Related Posts:

  • IMAGE function in Excel
    Excel IMAGE Function - Insert Images from a Cell Value
  • N Functions in Excel thumbnail
    N Function in Excel
  • Return non-adjacent columns FILTER function thumbnail
    Non-Adjacent Columns with FILTER Function
  • Aggregate rows in Power Query
    Aggregate Rows in Power Query

Reader Interactions

Comments

  1. Jadara says

    22 May 2021 at 6:27 pm

    Many thanks for your blog,, it´s really of great help.. (so the youtube sessión.. .please go on with it 🙂 ).
    I was wondering whether is possible to help me with this:
    my first condition is: Value to be null if that condition is true then enter the second condition:
    Country = Spain then “ES”
    Country = Portugal then “PT”
    else keep it as it.

    I have build following status:

    = Table.FromRecords(Table.TransformRows(RenCol,(row) => Record.TransformFields(row, {“Store”, each if row[Value]= null and row[OU]=”Spain” then “ES” else if row[Value]= null and row[OU]=”Portugal” then “PT” else _})))
    but I was wondering whether it could be possible to avoid to repeat twice the 1st condition “if row[value]= null” as a part of the general condition.
    Many thanks again for all your contribution to PQ 🙂
    all the best.
    Jadara.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Recent Posts

  • Excel SWITCH Function – Nested IF Function Alternative
  • Sum Formulas Only in Excel
  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Blog
  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·