Hello! In our last lesson, we saw how to enrich data by creating custom columns with arithmetic operations. You learned to calculate new values like Total Sale or Profit Margin, which is a powerful first step in creating new insights.
Now, we'll move from numerical calculations to logical rules. Business analysis isn't just about math; it's also about applying rules to categorize, flag, and segment data. This is where conditional logic comes in.
This lesson directly addresses the learning outcome: Create conditional columns using IF/THEN logic. We'll explore how to build columns that assign values based on specific conditions—for example, classifying a sale as "High Value" if it's over a certain amount, or flagging a project as "At Risk" based on its status. For the managers in your workshop, this skill is fundamental for turning raw data into actionable business intelligence.
By the end of this lesson, you will be prepared to teach your audience three different methods for creating conditional columns in Power Query, each suited for different scenarios:
- Using the simple Conditional Column graphical interface for standard IF/THEN/ELSE rules.
- Writing basic M language formulas in a custom column for more complex logic involving
ANDorORconditions. - Leveraging the intelligent Column From Examples feature to have Power Query generate the logic automatically.
The Concept of Conditional Logic
At its core, conditional logic is about applying a test and then performing an action based on the result. The most common form is an "IF/THEN/ELSE" statement:
- IF a condition is true...
- THEN do this...
- ELSE do something different.
Think of it like a prism sorting data. Raw, mixed data goes in, and the conditional logic acts as the prism, splitting it into distinct, meaningful categories based on the rules you set.
Method 1: The Standard Approach - The "Conditional Column" Interface
The most straightforward way to implement conditional logic in Power Query is through the dedicated "Conditional Column" feature. This provides a user-friendly dialog box where managers can build rules without writing any code.
You can find it on the Add Column tab in the Power Query Editor.
The Microsoft Learn documentation provides a clear, step-by-step guide on using this feature. It's an excellent resource to use in your workshop.
Add a conditional column - Power Query
This guide, 'Add a conditional column' from Microsoft Learn, walks through the process of creating a conditional column using the graphical interface. It starts with a simple IF/ELSE rule and then shows how to add more clauses for an IF / ELSE IF / ELSE scenario.
Read the sections 'Adding a conditional column' and 'Adding and organizing multiple clauses'. Pay close attention to how the dialog box is structured and how you can add multiple 'clauses' to handle more than two outcomes.
As you saw, this interface is perfect for creating categories. For example, you could set up rules like:
- IF
[SaleAmount]> 10000, THEN output "High Value". - ELSE IF
[SaleAmount]> 5000, THEN output "Medium Value". - ELSE output "Standard Value".
Method 2: Handling Complex Logic with AND/OR
A common question your managers will have is, "How do I check multiple conditions at once?" For instance, "IF [Country] is 'USA' AND [Status] is 'Shipped'".
The basic "Conditional Column" interface doesn't have explicit AND/OR dropdowns, but you can achieve this logic in two main ways. You should present both to your audience, as they cater to different comfort levels.
A) The Clever GUI Trick: Ordering Your Rules
You can simulate AND logic by carefully ordering your rules. Because Power Query evaluates the rules from top to bottom and stops at the first one that is true, you can nest conditions.
The following article explains this concept brilliantly.
Conditional Column in Power BI using Power Query
This article from RADACAD, 'Conditional Column in Power BI using Power Query', provides a fantastic explanation of how to handle AND/OR logic. We'll focus on the section that demonstrates how the order of conditions is key.
Read the section titled 'What about AND/OR?'. Focus on the subsection 'Order of conditions matters!'. The example shows how to determine a title based on Gender and Marital Status by structuring the IF/THEN clauses in a specific order, which implicitly creates the AND logic without writing any code.
This method is powerful but can be abstract. For managers who prefer a more direct approach, the next method is a great alternative.
B) The Low-Code Way: Writing a Custom Formula
For full control, you can use the "Custom Column" feature (which we used for arithmetic in the last lesson) to write an if statement manually. The syntax in Power Query's M language is slightly different from Excel but very readable.
This video provides an excellent walkthrough of the syntax for simple if, if and, if or, and nested if statements.
How to Write Power Query if Statements incl. Nested ifs, if or, if and with the IF function
This video from MyOnlineTrainingHub clearly explains how to write your own if statements. It's a perfect resource for demonstrating the 'low-code' approach to conditional logic.
Watch the video from 01:40 to 06:24. It covers four key scenarios you can teach: Key Differences from Excel (01:40): Note the lowercase keywords (if, then, else) and the absence of commas. IF OR (02:21): Shows how to use the or keyword to check if at least one condition is met. IF AND (03:52): Shows how to use the and keyword to ensure all conditions are met. Nested IFs (04:47): Demonstrates how to handle multiple levels of logic by placing an if statement after an else.
Method 3: The "AI" Approach - Column From Examples
One of the most impressive features for a non-technical audience is "Column From Examples." Instead of writing rules, you simply provide a few examples of the desired output, and Power Query's pattern detection will generate the transformation logic for you. This is essentially AI-powered conditional column creation.
Excel Power Query Tutorial - Add Column From Examples
In this video, David Langer demonstrates the power of 'Add Column From Examples'. This is a fantastic 'wow' moment for a workshop, as it feels like magic to new users.
Watch from 07:38 to 13:00. Focus on two examples: Simple Categorization (07:38): He transforms the numerical Survived column (0s and 1s) into text ('Perished' and 'Survived') by typing just one example of each. Multi-Category Transformation (10:54): He does the same for the P Class column (1, 2, 3), converting numbers to 'First Class', 'Second Class', and 'Third Class'.
This method is ideal for simple, repetitive rules and serves as a great introduction to the idea of AI assisting with data preparation.
Test your understanding!
You have a dataset of customer orders with a SaleAmount column. You need to create a new column called Priority to categorize each order. The rules are:
- Sales over $5,000 are "High".
- Sales between $1,000 and $5,000 (inclusive) are "Medium".
- Sales under $1,000 are "Low".
Using the "Conditional Column" graphical interface (Method 1), how would you set up the clauses?
Show answer
You would open the "Add Conditional Column" dialog and set up the clauses in the following order:
-
New column name:
Priority -
Clause 1:
- If column
SaleAmount - Operator:
is greater than - Value:
5000 - Output:
High
- If column
-
Clause 2:
- Else If column
SaleAmount - Operator:
is greater than or equal to - Value:
1000 - Output:
Medium
- Else If column
-
Final Else clause:
- Else:
Low
- Else:
The order is crucial here. By checking for "> 5000" first, the second clause only applies to values that are already known to be less than or equal to 5000, effectively creating the "between $1000 and $5000" logic.
Conclusion
You are now equipped with a full toolkit to teach your managers how to implement business logic in Power Query. By creating conditional columns, they can transform raw data into meaningful categories, flags, and segments that are essential for analysis and decision-making.
Key Takeaways for Your Workshop:
- Three Ways to Add Logic:
- Conditional Column GUI: The go-to method for straightforward IF/THEN/ELSE IF rules.
- Custom Column with Code: The "power user" method for explicit
and/orlogic and full control. - Column From Examples: The "AI" method that writes the rules for you based on patterns.
- Conditional logic is about categorizing and segmenting data to make it more useful for analysis.
- For complex logic in the GUI, the order of the rules is critical.
Preview of the Next Lesson:
Now that you know how to create categories like "High," "Medium," and "Low," what's next? The logical next step is to summarize your data based on these new categories. In our next lesson, we will explore how to group and aggregate data to create summaries, such as finding the total number of sales or the average revenue for each priority level you just created.