In the previous article, we looked at IF function basics. Today, it is time to look at more advanced stuff. Spoiler alert: nested formulas included.

For all the samples, we will be using the following image as basis:

A sample Excel file with applied
IF functions can be downloaded here:  
DOWNLOAD NOW FOR WINDOWS

Text Editor Is Better than Native Power Query “Editor”

Over the years, I started to used PSPad Editor for writing complex formulas. I use JavaScript notation because I like how the formula elements are highlighted. You can use any language you want. No choice is bad or correct. Totally up to you. So how does this work? It is simpler than you think. You just write your formula in the text editor of your choice and then you copy’n’paste the formula into the “Custom Column” window in Power Query.

Compare formula written in Power Query in Custom Column window:

vs formula written in a text editor:

The way how the complex formulas look makes really a difference.

AND Operator

You will use AND operator when you want to check for multiple conditions at the same time. Assume you want to add a new column based on this rule: If Campaign is “CZ-Generic” and Match Type is “Broad” then show value “Broad in CZ-Generic”. The formula would look like:

Power Query editor does not care about spaces or line breaks so you can really make your formula look nice. You will appreciate the look when you come back to a project several months later and when you start to decode the tricky steps in your queries 🙂

OR Operator

You will use OR operator when you want to check if at least some condition is met. Assume you want to add a new column based on this rule: If Match Type is “Broad” or Keyword contains “+” then show value “Broad keyword”. The formula would look like:

Combinations of AND and OR

Alright, so previous examples were still easy. Let’s make it harder. You can combine AND and OR to form any condition you can imagine by adding another layer of complexity: brackets. By adding brackets into the formula, you can define complex rules. Assume you want to check: if Campaign  is “CZ-Generic” and Match Type is “Broad” or another two conditions “CZ-Generic” and Keyword contains “+” are true then show “The keyword is a broad KW in CZ-Generic”

The formula would look like:

Notice how the brackets are used. Also notice that PSPad is counting brackets for you. When you select a bracket, it will highlight the remaining closing or opening bracket from that pair.

Then…

So far, for the “then” part of the formula, we have been using a static value. If you use the “IF” wizard, you can also reference another column. Of course, you reference another column just by typing it as e. g. [Keyword]. But what if you simply want to perform an action? Assume, you want to create broad KWs out of broad match modifier KWs, i. e. you need to remove all “+” signs from the Keyword column.

The formula would look like:

Or you want to create broad match modifier KWs from pure broad KWs:

Now, the last formula is trickier. You are checking for keywords which do not contain “+”, hence the “not” operator. Then you need to append “+” as prefix and join it with string where you replace spaces with space and “+” sign, hence the “&” as joining character. Makes sense?

Let’s take it one step further, the new Keyword you are creating needs to be all lower case. How would that look like?

Notice the added Text.Lower function in order to achieve all lowercase letters.

I am showing Text.Replace function in the “then” part of the formula. Frequently, you will also use Text.Start, Text.End, Text.Range or wide array of List functions (coming in a future article).

Takeaways

• By using and, or, not and brackets, you can create extremely complex if formulas.
• I strongly recommend using a text editor which color-codes the formula for you, especially when you are building nested formulas (the Text.Lower example).
• Keep in mind that if, and, or, not, then, else are always lowercase.
• Don’t limit yourselves with static values or column references in the “then” or “else” part of the formula.

A sample Excel file with applied
IF functions can be downloaded here:  
DOWNLOAD NOW FOR WINDOWS

And that’s it! Don’t be scared and try yourself ?

DZ

***Tip: Have a question? Ask in the comment section below or in this Facebook group. You can also sign up for our free newsletter.***

***Do you feel like you need more help? We can help you with PPC Automation and/or with your reporting under our new project MythicalReports.com here.***