Power BI offers top-of-the-line features for both beginners and power users. With a relatively low learning curve and its strong integration capabilities with Microsoft Apps, Power BI is a fantastic data visualization tool to explore your data and create engaging reports. Within Power BI is a lightweight tool called Power Query to transform and shape data tables.
One such data shaping tool in Power BI is Power Query IF Statement, which makes data transformation easy and allows you to compare values. IF Power Query also simplifies complex data transformations. Using Power Query IF statements, Power BI users can slice data fields, retain relevant information, derive and create new parameters, and sort data for more detailed analysis.
This guide introduces you to Power Query IF statements with conditional and custom columns and common operators you can use to create conditional Power Query IF statements.
What is a Power Query IF Statement?
Power Query IF statement is one of the many ways to transform your data. Similar to the IF statement in Microsoft Excel, the IF statement Power Query function checks a condition and returns a value depending on whether the result is “true” or “false”.
In Power BI, IF statements can be used as both DAX functions and Power Query conditional columns. In this guide, we’ll be confining ourselves to the IF statement in Power Query. For the DAX version of the Power BI IF Statement, we have a detailed guide you can check out here – How to Use Power BI IF Statement: 3 Comprehensive Aspects. You can avail more information on DAX functions in Power BI here- Understanding DAX Power BI: A Comprehensive Guide.
How to Use Power Query IF Statements?
Power Query offers you two options to write Power Query IF statements:
Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.
Start for free now!
Get Started with Hevo for Free
Power Query IF Statement: Syntax
If you would like to write the IF statement Power Query Command in your formula editor (using a custom column), you can refer to the following syntax for defining your conditional expressions.
if condition then
true expression
else
false expression
Please note that Power Query IF statements are case-sensitive and the words if…then…else are written in lowercase.
Let us look at an example to understand the IF Statement in Power Query:
let
value = 10,
result = if value > 5 then "Greater than 5" else "Less than or equal to 5"
in
result
In this example, if the value is greater than 5, the result will be “Greater than 5”; otherwise, it will be “Less than or equal to 5”.
Let’s look at how to use Power Query IF statements with the Conditional Column Feature.
Using Conditional Column For Basic Power Query IF Statement Logic
With the conditional column feature, Power Query IF statements like—Power Query IF THEN, Power Query IF OR, Power Query IF AND, and Power Query IF NULL becomes much easier to define. Even more so than the Excel equivalents.
To use the conditional column, you can visit Add Column > Conditional Column in your Power Query pane. This approach of Power Query IF statements allows you to define basic-if statements.
Consider this sales data example to help understand the conditional column feature for basic Power Query IF Statement logic.
The sample file used for this example can be found here – Power Query IF Statement-Example File. You can import this file to your Power Query editor by selecting any cell in this table and clicking Data -> From Table/Range to load the data into Power Query.
In this example, we are required to add a new column called Incentive based on the following conditions:
- If the Sales Value is > $6500, the incentive given will be $300.
- If the Sales Value is < $6500, the incentive given will be $200.
To use the Power Query editor window, we first need to enable editing for your sales data table. For that, visit Home > Edit Queries.
Now, visit the tab Add Column > Conditional Column to define your Power Query IF statement. A dialog box of “Add Additonal Column” will appear on the screen, where you can insert the new column “incentive”. A new window will appear as shown below.
The next set of tasks is fairly simple. All you have to do is define your Power Query IF statement, using the drop-down options in the window.
Type in your new column name under the heading New column name. Since we are offering an incentive of $300 for sales value > $65000, we’ll establish our IF statement Power Query conditional as:
If Sales Value is greater than 6500 then Output is 300 Else 200.
Which translates to:
You can use this menu to define and use basic IF statement logic. The available options and their input fields are as follows:
- New Column Name: Defines the column name for your new column.
- Column Name: The column name which is evaluated against your Power Query IF statement.
- Operator: Mathematical operation which compares text, numbers, or date data type present in your column name using less than, greater than, equal to operations, etc.
- Value: Parameter or value against which the comparison is made.
- Output: Value or parameter returned when a condition is met.
- Else: Value or parameter returned when a condition is not met (when previous conditions get false).
Click OK to apply changes and add a new column, “incentive” to your sales table. Your first conditional column feature for basic Power Query IF statement logic is now complete. Your new column will be visible as soon as you leave your conditional column window.
Please note that the conditional column feature supports basic Power Query IF statement logic; the ones which can be “fairly” expressed as a single sentence in English. For more granular and complex conditional statements, we recommend you take advantage of the custom column feature or formula editor, as described in the next section.
Using Custom Column For More Advanced IF Statement Power Query Logic
Your usual day data table transformations won’t be as easy as previously described. Even simple Power Query IF statement conditions like dividing A by B when the result is less than C would require you to write an IF statement in the Power Query editor.
Custom column option can be accessed in your Power Query under the tab Add Column > Custom Column. When you click on the Custom Column option, a new window will open with space to define and write your new IF conditional expressions.
Since our daily conditional expressions are more complex, let’s revamp our original problem to reflect a pragmatic setting.
Let’s say you own a business, and you want to incentivize your sales representatives based on their locations. You decided to reward your sales representatives residing in the South region who’ve produced more than $6500 sales value with a $400 dollar prize. For the rest, the conditions remain the same.
Our Power Query IF statement for a new condition, if stated in plain English, would look like:
If Sales Value is greater than 6500 and Region is South, then Output is 400
Else Sales Value is greater than 6500, then Output is 300.
Else 200.
Putting this into our Power Query editor, with if..then..else in lowercase, we get:
Hit Home > Close and Apply to save your changes. You have now successfully used a custom column for more advanced IF statement Power Query logic.
Types of Power Query IF Statements
Power Query IF statements come in different forms:
- Power Query IF OR
- Power Query IF AND
- Power Query IF NULL
- Power Query nested IF
Power Query IF OR Statement
Power Query IF OR specifies two conditions to be evaluated (separately) for stating them as true or yielding the desired output. The others are stated false and returned with a different value or parameter. In other terms,
= if something is true or something else is true then “true” else “false”
Extending on our previous sales data, if you wish to incentivize sales representatives operating in south or central regions with $350, and the rest with $200, you can run a Power Query IF OR query as follows:
=if [Region] = "South" or [Region] = "Central" then 350 else 200
Power Query IF AND Statement
Power Query IF AND specifies two conditions to be evaluated (simultaneously) for stating them as true or yielding the desired output. The others are stated false and returned with a different value or parameter. In other terms,
= if something is true and something else is true then “true” else “false”
If you wish to incentivize sales representatives operating in south region having sales value of more than $6500 with $450, and the rest with $200, you can run a Power Query IF AND query as follows:
=if [Region] = "South" and [Sales Value] > 6500 then 450 else 200
Another example can be if you wish to provide a bonus to sales representatives operating in the central region having a sales value of more than $6500 with prize money of 0.5% of sales value, then your IF AND query will look like this:
=if [Region] = "Central" and [Sales Value] > 6500 then [Sales Value] * 0.005 else 0
Power Query IF NOT Statement
Power Query IF NOT checks a condition if it’s true or not. If it’s TRUE, the operator returns FALSE, and if given FALSE, the operator returns TRUE. So, basically, it will always return a reverse logical value.
= if not something is true then “true” else “false”
Let us assume you just want to reverse what you did in your earlier example. You wish to award bonuses to all the other sales representatives who are not residing in the south region having sales value of more than $6500. Using the IF NOT statement, you can run a Power Query conditional statement as:
=if not ([Region] = "South" and [Sales Value] > 6500) then 450 else 200
Power Query Nested IF Statement
Analogous to Microsoft Excel, nested IF statements are IF statements contained within other IF statements. These nested IF statements can be used to return a TRUE or FALSE, which can be further used as inputs to other IF statements.
Here’s an example to clarify nested IF statements in Power Query.
Suppose you wish to boost sales efforts in the central region by rewarding a bonus of 0.5%, in the west region by rewarding a bonus of 0.3%, and in the south region by rewarding a bonus of 0.2% of sales value. For such a case, your nested IF statement would look like this:
= if [Region] = “Central” then [Sales Value] * 0.05
if [Region] = “West” then [Sales Value] * 0.03
if [Region] = “South” then [Sales Value] * 0.02
To make nested Power Query IF statements work, place the second if statement after the first otherwise clause. The formula in this example is created with space and line breaks. This increases readability while still performing appropriately.
Common Operators in Power Query IF Statements
Till this point, we’ve discussed basic logic IF statements to simply compare two quantities. Power Query IF statements offer a plethora of mathematical operators to help tailor-craft your conditional statements as per your needs. These include:
- “=” is equal to
- “<>” is not equal to
- “>=” is greater than or equal to
- “<=” is less than or equal to
- “>” is greater than
- “<” is less than
- “+” for sum
- “-” for difference
- “*” for product
- “/” for quotient
- “+x” for unary plus
- “-x” for negation
These mathematical operators can be used while writing your IF conditional statements in Power Query editor (custom column method).
For example, if you want to verify whether a product’s revenue is exactly $500, you can utilize the equal operator as follows:
if [Revenue] = 500 then ... else ...
To categorize individuals aged 18 or younger as “Youth” and those older as “Other”:
if [Age] <= 18 then "Youth" else "Other"
The concept is clear. Now, what if you wish to merge several conditions?
These mathematical operators can be employed when crafting your IF conditional statements within the Power Query editor, particularly when using the custom column method.
Conditional Column Limitations
Conditional Columns are useful in simple cases. However, they are only suitable for sophisticated applications. Some of the constraints include:
- It is not feasible to combine numerous criteria with and/or logic.
- Cannot use the not operator to reverse the true/false result.
- Can only return the results given. Additional query steps are necessary to use the if statement’s result.
For more sophisticated circumstances, we must write the M code within a Custom Column.
Working with Different Data Types
Here, our emphasis shifts towards utilizing IF statements with diverse data types, including text and dates, to enhance your capabilities in data manipulation.
Exploring Text Manipulation
As we continue our exploration of IF statements, we delve into the realm of text values. For example, when examining how to identify if a product name includes a particular keyword and subsequently categorize it, you can employ the Text.Contains
function:
if Text.Contains( [Product Name], "Widget") // Does [ProductName] contain "Widget then "Widget" // if yes, return "Widget" else "Other" // else return "Other"
Alternatively, you can verify if the fusion of two text fields corresponds to another field:
if [FirstName] & [LastName] = [FullName] then true else false
Furthermore, you have the option to yield a value when a segment of a text string aligns with your criteria. For instance, consider the scenario where invoices commencing with the text “MAR” pertain to market revenue:
if Text.Start( [InvoiceID], 3 ) = "MAR" then "Marketing Revenue" else "Other"
Working with Dates
Dates represent another prevalent data type frequently encountered in Power Query. IF statements can be effectively employed to manipulate and classify dates. For instance, you can ascertain whether a date falls within a defined range:
if [OrderDate] >= #date(2023, 1, 1) and [OrderDate] <= #date(2023, 12, 31) then "2023 Order" else "Other Year"
Alternatively, you can classify dates based on the day of the week using the Date.DayOfWeek function:
if Date.DayOfWeek([OrderDate]) = 0 then "Sunday" else if Date.DayOfWeek([OrderDate]) = 6 then "Saturday" else "Weekday"
Advanced IF Statement Techniques
Within this section, we delve into more sophisticated strategies for utilizing IF statements in Power Query. We will explore the equivalent of the “in” operator, the coalesce function, and the type compatibility operator.
Equivalent of the “in” Operator
Numerous programming languages feature the “in” operator, which allows you to verify the presence of a value within a list. It essentially condenses a series of OR-statements into a concise syntax. This proves advantageous when you need to classify data based on a predefined collection of values.
For instance, suppose you possess a list of preferred customers and wish to introduce a column into your sales data indicating whether a customer belongs to the group of favorites or not. While Power Query lacks a built-in “in” operator, you can emulate its functionality by utilizing List.Contains
:
if List.Contains( {"Alice", "Bob", "Charlie"}, [Customer Name] ) then "Favorite" else "Regular") /* ------------------ -- is identical to ------------------ */ if [CustomerName] = "Alice" or [CustomerName] = "Bob" or [CustomerName] = "Charlie" then "Favorite" else "Regular"
List.Contains
verifies whether the “CustomerName” is present in the list of preferred customers. Although this example employs just three list values, you have the flexibility to extend the list with as many values as needed. You can even reference a column containing values for validation!
Coalesce Operator
The coalesce function retrieves the initial non-null value from a sequence of expressions. This proves beneficial in scenarios where you have several columns that might contain missing data, and you wish to obtain the first available value.
Consider a scenario where you have a table consisting of three columns: “PrimaryPhone,” “SecondaryPhone,” and “TertiaryPhone.” Your objective is to introduce a new column that presents the initial available phone number for each record.
Power Query utilizes the ?? construct as the Coalesce operator. You can employ the coalesce operator alongside an IF statement to accomplish this task:
[PrimaryPhone] ?? [SecondaryPhone] ?? [TertiaryPhone]??"No Phone" /* ------------------ -- is identical to ------------------ */ if [PrimaryPhone] = null then if [SecondaryPhone] = null then if [TertiaryPhone] = null then "No Phone" else [TertiaryPhone] else [SecondaryPhone] else [PrimaryPhone]
Type Compatibility Operator
Occasionally, when dealing with columns that contain a mix of data types, your IF statements may encounter errors. In such cases, you can employ the type compatibility operator to assess whether a value corresponds to a particular data type.
For instance, consider a column named “Data” that contains various data types. Your objective is to generate a new column that classifies the data as either “Numeric,” “Text,” “Date,” or “Other”:
if [MixedData] is number then "Numeric" else if [MixedData] is text then "Text" else if [MixedData] is date then "Date" else "Other"
In this illustration, the “is” operator is employed to assess whether the values in the “Data” column correspond to numeric, textual, or date types. This approach enables the management of mixed data types without triggering errors.
With these advanced IF statement techniques in Power Query at your disposal, the next topic on our agenda involves addressing potential error messages you may encounter.
Error messages
In Power Query, as well as Excel, there exist subtle yet critical distinctions within their conditional statements. Small oversights can readily lead to errors in Power Query, and the error messages generated are often not particularly informative. Let’s delve into some prevalent error messages and their underlying causes.
1. Token Eof expected
This error, “Token Eof expected,” may occur when there is a discrepancy in capitalization or an incorrect function name is used. For instance, you must use lowercase for words such as “if,” “then,” and “else” to create a functioning formula.
If any of these letters are capitalized in the Custom Column box, Power Query will trigger this error. The following example illustrates the use of a capitalized “IF,” resulting in the error message: “Token Eof expected.”
Expression.SyntaxError: Token Comma expected
This error might occur while editing your formula in the formula bar. Interestingly, the error message suggests a missing comma is causing the issue, which may not be the actual problem. For instance, using a capitalized “IF” can lead to this error message.
Expression.SyntaxError: Token Literal expected
This error signifies that the formula anticipates a condition, value, column name, or function somewhere within it but does not find one. When adding conditions to your formula that involve words like “not,” “and,” or “or,” you may encounter this error. If you use these words without providing the required conditions, the error appears.
Expression.SyntaxError: Token Then/Else expected
These errors arise when the words “then” and “else” are missing or improperly placed within the IF function. In Power Query, “then” and “else” serve as separators between arguments within the IF function. When crafting nested IF statements, each statement should include both a “then” and an “else” clause. If you omit these words or replace them with separators, you’ll encounter one of these error messages:
- Expression.SyntaxError: Token Then expected
- Expression.SyntaxError: Token Else expected
These error messages refer to missing or expected tokens, where a “token” signifies a specific word or symbol in the Power Query language, like “then” or “else.” When the error message mentions a missing or expected token, it means that a particular word or symbol is either missing or misplaced in your formula.
Recommended Blogs
Conclusion
We hope this comprehensive piece provided a lucid explanation around Power Query IF statements, and that you are now ready to write and use your own customized IF conditional statements. We showed you two ways to use Power Query IF statements—one using conditional column which is useful for basic IF statement logic and, the other using custom column which is valuable when using advanced IF statement logic.
Power Query in Power BI constructive tool for importing data from a variety of sources. While Power Query is just limited to Excel sheets and CSV file formats, why not import data from Databases like MySQL and PostgreSQL, SaaS applications like Mailchimp, Zendesk, and CRMs like Salesforce, and HubSpot to Power BI? Wondering how this is possible?
Hevo Data is a No-Code and Zero Data Loss Solution that supports data ingestion from multiple sources be it your frequently used databases and SaaS applications like MySQL, PostgreSQL, Salesforce, Mailchimp, Asana, Trello, Zendesk, and others.
Using Hevo is simple, and you can set up a Data Pipeline in minutes without worrying about any errors or maintenance aspects. Hevo also supports advanced data transformation and workflow features to mold your data into any form before loading it to the target database.
Visit our Website to Explore Hevo
Hevo lets you migrate your data from your favorite applications to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt, within minutes to be analyzed in Power BI.
Why not try Hevo and the action for yourself? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also check out our pricing plans to choose the best-matched plan for your business needs.
Have more ideas or Power BI features you would like us to cover? Drop a comment below to let us know.
With a background in marketing research and campaign management at Hevo Data and myHQ Workspaces, Divyansh specializes in data analysis for optimizing marketing strategies. He has experience writing articles on diverse topics such as data integration and infrastructure by collaborating with thought leaders in the industry.