In a previous post, we briefly looked at the if statement in Power Query, now we’re going to dig a bit deeper and understand how it really works.
In Excel, IF is a core function, it’s one of the first ones we learn. Last week, we looked at Functions in Power Query, but we didn’t cover a Power Query version of the IF function. Surely there is one… right? …well, kind of.
To use if logic in Power Query, we need a more programming based methodology rather than a function based methodology we find in Excel. If you’ve ever programmed VBA or other languages, you will be familiar with this approach. However, if you’re coming from a purely Excel world this may be new to you.
There are two options:
- Using the conditional column feature for basic if logic
- Writing M code for more advanced if logic.
We’ll cover both in this post.
Downloads
To work along with the examples below, download the sample files. Click here, to subscribe and get access to the Downloads section.
The examples in this post use the file called Example 15 – If Function.xlsx
The Example
The file contains just one Table. Select any cell in that Table and click Data -> From Table/Range to load the data into Power Query.
In this example, we will be calculating the value assuming the following:
- Scenario 1: Sundays have an additional 10% premium (requires basic logic)
- Scenario 2: Sundays have an additional 10% premium with two products (Tiger & Farmhouse Bloomer) also attracting a 5% discount on that day (requires advanced logic).
IF logic using a conditional column
Let’s start with Scenario 1 and use the Conditional Column feature.
Scenario 1 – Sunday Premium
Select the Date column, then add a column containing the weekday by clicking Add Column -> Date -> Day -> Day Name.
Next, click Add Column -> Conditional Column
Enter the following options on the Add Conditional Column dialog box:
New Column Name: Sunday Premium
If Day Name equals Sunday then 1.1 else 1
Click OK to accept the formula.
In the scenario above, we have only used a few of the options available in the Conditional Column dialog box.
Other operators can be used:
- Equals
- Does not equal
- Begins with
- Does not begin with
- Ends with
- Does not end with
- Contains
- Does not contain
We can click the 123ABC button to insert column values, or parameters, instead of hardcoded values.
Also, we can create as many if statements as we want by clicking the Add rule button. The if statements execute in turn, so if the first if statement is not triggered, it will move to the second, then the third, and so on.. However, if the first if statement is true, the remaining logic is skipped over.
Finally, let’s add a formula. Click Add Column -> Custom Column to display the Custom Column dialog box.
Enter the following formula:
=[Value] * [Sunday Premium]
Call the formula Adjusted Value, then click OK.
Alternatively, for this last stage, you could have used a multiplication column Add Column -> Standard -> Multiply, but I prefer the Custom Column approach.
Conditional logic is pretty easy… right.
Scenario 2 – Sunday Premium & Product Discount
For Scenario 2, things get tricky…very tricky.
I could go through a long list of transformations to illustrate how to do it, but I’m not going to. The Conditional Column feature is really for basic requirements; it doesn’t allow us to use AND or OR logic.
Instead, we are going to turn to option 2, which is writing the M code ourselves.
IF logic by writing the M code
Before launching into the examples, let’s take a few minutes to understand how if works.
In an Excel world, the IF function would be written as:
=IF( [thing] = "thing 2", [do this if true] , [do this if false] )
So, if you are coming from an Excel world it might be easier to think of it like this:
- remove the opening and closing brackets
- change the first comma for then
- change the last comma for else
- change IF for if
This would create the following the M code
= if [thing] = "thing 2" then [do this if true] else [do this if false]
Once you’ve been through it a few times, you’ll get then hang of it. It has the added advantage that it sounds like a standard English sentence.
To nest an if within another if we use the same if – then – else methodology, as shown below
= if [thing to to test #1] = "something else" then [do this if true] else if [thing to to test #2] = "something else" then [do this if true] else [do this if false]
There can be as many nested ifs as we want. This works in a similar way to as adding new rules into a Conditional Column.
Using AND or OR logic is pretty simple too, we just use the word and / or in the code. The code below demonstrates the use of and logic.
= if [thing to to test #1] = "something else" and [thing to to test #2] = "something else #2" then [do this if true] else [do this if false]
Where there is a mix of both and/or logic, brackets are required to ensure the logic evaluates in the correct order.
That’s covered the basics, now time for the examples.
Start from scratch by loading the Table into Power Query again. Then add the Week Day name column as we did in the example above.
Scenario 1 – Sunday Premium
Add a Custom Column to the table by clicking Add Column -> Custom Column
Use the following formula:
= if [Day Name] = "Sunday" then [Value] * 1.1 else [Value]
Pay close attention to the words if, then and else, they are all lower case. Power Query is case sensitive, so if we get this wrong, the formula will not work.
That’s it. Click OK to add the formula.
Scenario 2 – Sunday Premium & Product Discount
There are a few ways to write this formula. Ultimately there are three possible results for this scenario:
- Sunday premium & product discount
- Sunday premium only
- No premium or discount
Solution #1:
= if [Day Name] = "Sunday" and ([Product] = "Tiger" or [Product] = "Farmhouse Bloomer") then [Value] * 1.1 * 0.95 else if [Day Name] = "Sunday" then [Value] * 1.1 else [Value]
We do need to include some brackets in here to ensure the formula executes in the right order. We want the or to be calculated first, so we put this section into brackets.
Solution #2:
Here is another solution you could try.
= if [Day Name] = "Sunday" then if [Product] = "Tiger" or [Product] = "Farmhouse Bloomer" then [Value] * 1.1 * 0.95 else [Value] * 1.1 else [Value]
Conclusion
Conditional logic using an if statement in Power Query is different to Excel. However, it’s not difficult to understand, so all it needs is practice.
Series Contents
- Introduction
- Import Data
- Data Refresh
- Edit Queries
- Close & Load Options
- Using Parameters
- Basic Transformations
- Combine / Append Queries
- Import All Files in a Folder
- List All the Files in a Folder and File Attributes
- Import Data from the Current Workbook
- Import Data from the Web
- Unpivot Data
- Unstacking Data in a Column
- Lookup Values using Merge
- Change the Source Data Location
- Formulas
- If Statements for Conditional Logic
