Before writing complex IF statements in Power Query, users should try creating conditional columns that use a simple set of dropdown menus to automatically generate formulas for you. We’ll explain how to take advantage of this powerful feature, as well as the standard IF statement syntax in Excel Power Query for those who want to write their own formulas.
IF statements are a cornerstone of data analysis using Microsoft Excel. Power Query uses a slightly different syntax than working with regular spreadsheets but can also be generated with a built-in template called a Conditional Column.
Let’s jump in!
Table of Contents
Why You Should Create IF Statements in Power Query
Power Query is one of the best, yet most underutilized features of Microsoft Excel. When creating IF statements in Power Query, they will automatically fill down helping you avoid formula errors, and also let you create intermediate columns tracked by sequential applied steps to avoid having to write overly complex and hard to manage formulas in a standard Excel worksheet.
Launching the Power Query Editor
To create a conditional column in Excel Power Query, first launch the Power Query Editor by going to the Data section of the Power BI Ribbon and either Get Data from an external source, or from a Table/Range like the example below.
A new window will pop up for the Power Query Editor interface. From here you can create formulas, or conditional columns to create IF statements similar to what you would in a regular worksheet but with a different syntax.
How to Create Excel Power Query Conditional Columns
Excel users have two options for creating IF statements in Power Query, they can either write them using Power Query M code, or they can create a conditional column that will guide you through the process without having to know any code at all.
To create a conditional column in Power Query for Excel, launch the Power Query Editor and navigate to the Add Column section of the Power Query ribbon. Select Conditional Column.
A window will popup that gives you the ability to generate an IF statement that will fill out the contents of a new column.
Step 1.) Add a Conditional Column
Step 2.) Fill out the Add Conditional Column Screen
The Add Conditional Column screen uses drop down menus for you to fill out an IF statement. The Syntax you are re-creating is IF abc THEN xyz ELSE lmnop. Another way to look at it is IF true, then x, else y if false.
Column Names, Operators, and Values are selected like the in the image below.
The default operator is Equals but can be modified to test whether a column contains, begins with, or ends with specific values etc. This is much easier than writing specific formulas using Power Query M.
You can also add multiple criteria, by clicking on the “Add Clause” button. It will add additional rows that you can fill out with new criteria.
When you have filled out all of the criteria needed, the bottom section of Else provides the default value when no other conditions listed above are met.
Tip: Rather than returning specific values, you can return the value in another column. Click the ABC123 button next to a Value or Output to toggle between text or number output and select specific columns like the example below.
The ability to reference values from other columns is one of the best parts of the conditional column creator. It’s also a way to dynamically consolidate column and create groups when needed.
Step 3.) Review Results and Applied Steps
After clicking OK to finalize the conditional column, a new Applied Step will be created along with a filled-out column. Review the column preview to make sure it meets expectations, and you can always go back and make changes by clicking the cog to the right of the Applied Step.
Conditional Columns are our favorite way to create IF Statements in Excel Power Query because of the ease of use but you can also create IF statements using Power Query’s M formula language.
How to Create IF Statements in Excel Power Query Using M
To create an IF statement in Power Query, the syntax for the formula is: if [column] = “criteria” then “true” else “false”. Power Query is case sensitive so the if then else portions of the IF statement must be lower case. Text should be surrounded in double quotes while numbers do not.
The following example is a simple IF statement in Power Query M.
if [State] = "OR" then "West" else "Other"
When added to a Custom Column, the formula will highlight in colors when it is correctly being written, and there will be a check mark at the bottom of the Custom Column editor indicating that the syntax is correct.
After clicking OK a new column with the IF Statement will be generated.
Note: Once you finalize an IF statement using the custom column editor, it will open up as a conditional column when you click on the settings of the applied step. To get around this you can edit the formula in the formula editor or launch the Advanced Editor to modify the code.
IF Statements with Multiple Conditions in Excel Power Query
Creating IF Statements with multiple criteria is a common scenario in Power Query M. The language supports the ability to use multiple criteria, operators, and nest if statements together. The following example highlights how to setup an if statement that checks for multiple possible criteria then returns a result.
if [State] = "OR" or [State] = "WA" or [State] = "CA" then "West" else "Other"
The column name and condition being checked for must be independent of the condition before it, making it required to name the column each time you set another criteria.
Tip: To make if statements easier to read, you can enter them on multiple lines of the Custom Column Editor in Power Query. You can also copy and paste lines of a formula to avoid having to re-type the entire line multiple times.
There are numerous ways you can combine if statements and setup conditional logic. Entire books have been written in regard to Power Query M, and Microsoft publishes a comprehensive guide on the topic. Power Query M formula language reference – PowerQuery M.
If you still have trouble writing a formula just the way you want it, you may want to try advanced AI tools like ChatGPT or Excel CoPilot that can write formulas for you and will even explain the syntax and reason why they are written the way that they are.
Conditional Columns and Power Query IF Statements go hand in hand. While the first method gives you an easy-to-use interface to generate formulas behind the scenes without coding, using the Power Query formula editor gives you extremely granular control and can be faster when you’re familiar with the syntax. If you do decide to go the formula route, just remember to keep the parts of the if statement in lower case to avoid any potential errors!