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. 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, a self-service data preparation tool for the Power BI family, Power Query IF statements with conditional and custom columns, and finally common operators that you can use to create conditional Power Query IF statements.
Table of Contents
What is Power BI?
Image Source
The Gartner Magic Quadrant Report has rewarded Microsoft Power BI as the leader in the Business Intelligence industry for 14 consecutive years. Clearly, that explains a lot about Power BI.
Power BI is a Microsoft Business Intelligence suite to analyze data and share insights. It features capabilities such as:
- Dataset filtration,
- Visual-based data discovery,
- Interactive dashboards,
- Augmented analytics,
- Natural Language Q & A Question Box,
- Office 365 App Launcher, and many more.
Microsoft Power BI runs on desktop and mobile, on the cloud, which means your teams can collate, manage, and analyze data from anywhere. Power BI allows you to upload data from multiple sources like Excel, CSV, SQL Server, MySQL database, PDF, Access, XML, JSON, and a plethora more.
Microsoft Power BI collects, analyzes, and transforms your data into actionable insights. These insights are frequently provided using aesthetically appealing and simple-to-understand charts and graphs, which enables faster decision-making in your organization. When combined with Azure Cloud, Power BI can accelerate big data preparation and analysis and reduce your time to decision planning tremendously.
For more information on Power BI, do check out Understanding Microsoft Power BI: A Comprehensive Guide. If your organization uses Microsoft Azure cloud to store, manage and access information, you can combine your Azure cloud with Power BI using this guide – Connect Azure to Power BI: A Comprehensive Guide.
Business Benefits of Using Power BI
- Interactive & Easy-to-Use Interface: Nothing can be more beneficial than a simple-to-use interface with a drag and drop functionality that lets you create data visualizations using a few clicks. Microsoft Power BI enables everyone at every level of your organization to make confident decisions using up-to-the-minute analytics.
- Multiple Dataset Sources: Using Power BI, you can import data from a plethora of data sources, with support for both structured and unstructured data.
- Industry-leading AI: Microsoft’s strong base in artificial intelligence enables Power BI users to prepare data, build machine learning models, and find insights quickly from both structured and unstructured data.
- Exceptional Excel Integration: With Power BI, your users can easily collect, analyze, publish, and share Excel business data. Excel queries, data models, and reports can be readily connected to Power BI Dashboards by anybody who is acquainted with Office 365.
- Real-time Stream Analytics: Power BI fetches real-time data insights into your data visualizations to keep your teams up-to-date and ready to make the right decisions.
- Turn Insights to Action: Using Microsoft Power Platform, your teams can deliver actions quickly by combining Power BI with Power Apps and Power Automate. Using Microsoft’s strong integration, your users can easily build business applications and automate workflows.
What is a Power Query?
Image Source
Power Query is an intelligent data transformation and data preparation tool offered as part of Microsoft Excel and Microsoft Power BI. Power Query simplifies the process of importing data from multiple file formats like Excel tables, CSV files, database tables, webpages, etc. and allows you to transform your data into the right shape and condition for better analysis.
Once you have set up your Power Query operations, you don’t have to perform the same set of processes again on your new data. Using Power Query, you can easily set up and automate the same data transformation processes and yield the same data outputs as done previously.
Power Query functionality in Microsoft Power BI allows you to perform extensive data transformations such as:
- Deleting unnecessary columns, rows, or blanks.
- Data type conversions – text, numbers, dates.
- Splitting or merging columns.
- Using Power Query IF statements to sort & filter columns.
- Adding new calculated columns.
- Aggregating or summarizing data, and many more.
Hevo Data, a No-Code Data Pipeline, helps to transfer data from 100+ sources to a Data Warehouse/Destination of your choice and visualize it in your desired BI tool such as Power BI. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without even having to write a single line of code.
It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on the key business needs and perform insightful analysis by using a BI tool of your choice.
Get Started with Hevo for Free
Check out what makes Hevo amazing:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and your data volume grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
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 separate detailed guide that 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:
- Using Conditional Column For Basic Power Query IF Statement Logic.
- Using Custom Column For More Advanced IF Statement Power Query Logic.
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 “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’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.
Image Source
Consider this sales data example to help understand the conditional column feature for basic Power Query IF Statement logic.
Image Source: Self
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.
Image Source
Now, visit the tab Add Column > Conditional Column to define your Power Query IF statement and insert the new column “incentive”. A new window will appear as shown below.
Image Source
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:
Image Source
Reasonably straightforward right. 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.
Image Source
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.
Image Source
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:
Image Source
To distinguish the difference between new incentive plans and old incentive plans, we have named this new custom column as Incentive 2, as opposed to the original Incentive 1.
Here’s how both new columns will stack up. You can see the change in rewards, for sales representatives like Roshan, who was getting $300 with the original scheme and $400 with the new incentive scheme.
Image Source
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).
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 other 100+ data sources. Hevo migrates your data to a secure central repository like a Data Warehouse in minutes with just a few simple clicks.
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.