Master Power Query IF Logic: From Conditional Columns to M Language Custom Scripts

In the Excel ecosystem, the IF function is arguably the backbone of spreadsheet logic. It is likely the first advanced function any data enthusiast masters to control data flow. However, when users migrate their workflows to Power Query—Microsoft’s robust data extraction and transformation (ETL) engine—they often hit a wall. The familiar logic seems to disappear, replaced by a new interface and a case-sensitive language.

The reality is that Power Query handles conditional logic differently than the standard Excel grid. If you are looking to clean messy datasets or apply dynamic pricing rules using “If… Then…” logic, you need to adapt to the M Language environment.

This guide by Thủ Thuật serves as your comprehensive handbook. We will dissect two core methods for applying conditional logic: the user-friendly Conditional Column interface (perfect for beginners) and the powerful Custom Column with M Code (essential for advanced data professionals).

The Paradigm Shift: Why Power Query Logic Feels Different

In traditional Excel, you are likely accustomed to the formula: =IF(logical_test, value_if_true, value_if_false). It is forgiving, case-insensitive, and cell-based. Power Query, however, operates on columnar data and utilizes the M formula language, which leans much closer to actual programming.

To implement conditional logic, you have two primary distinct paths:

1. Conditional Column: A GUI-based approach that requires zero coding. It is excellent for simple, linear rules.
2. Custom Column (M Code): A script-based approach allowing for complex, nested, and multi-variable logic (AND/OR operations).

We will explore both methods using a practical business scenario to ensure you understand not just the how, but the why.

Setting Up the Practical Scenario

To make this tutorial actionable, let’s assume we are managing a sales dataset. You can load this data into Power Query by selecting your Excel range and clicking Data > From Table/Range.

The Business Problem: We need to calculate a new “Adjusted Price” for orders based on specific dynamic rules:

• Scenario 1 (Basic): If an order was placed on a Sunday, apply a 10% surcharge (Premium).
• Scenario 2 (Advanced): If the order is on a Sunday, apply the 10% surcharge, UNLESS the product is “Tiger” or “Farmhouse Bloomer”. In those specific product cases, apply a 5% discount on top of the surcharge.

Animation showing the sample dataset containing date and product columns

This dataset contains dates and product names, which is all we need to trigger our logic.

Method 1: The “No-Code” Approach (Conditional Column)

For users who prefer a visual interface, the Conditional Column feature is the fastest way to implement basic logic. We will use this to solve Scenario 1 (The Sunday 10% Surcharge).

Step 1: Pre-processing Date Information

Raw data often comes in a standard Date format (e.g., 12/05/2025). Power Query needs to explicitly know the “Day Name” to check if it is a Sunday. While you can write code for this, the menu offers a quicker way.

1. Highlight the Date column.
2. Navigate to the Add Column tab on the ribbon.
3. Select Date > Day > Day Name.

This action creates a new column populated with text values like “Monday”, “Sunday”, etc.

Screenshot demonstrating how to extract the day name from a date column

Step 2: Configuring the Conditional Column

Now that we have the text identifier for the day, we can set the rule.

1. Go to Add Column > Conditional Column.
2. In the dialog box, configure the following parameters:
   • New column name: “Sunday Premium”.
   • Column Name: Select Day Name.
   • Operator: Select Equals.
   • Value: Type Sunday. Crucial Note: This is case-sensitive. “sunday” will fail; “Sunday” is required.
   • Output: Enter 1.1 (representing 110% or a 10% increase).
   • Else: Enter 1 (keeping the price unchanged for other days).

Dialog box showing the setup for a conditional column rule

The Conditional Column interface is surprisingly versatile. Beyond simple equality, it supports operators such as Does not equal, Begins with, Ends with, and Contains. If you have multiple tiers of logic (e.g., Saturday gets 5%, Sunday gets 10%), you can simply click the Add rule button to stack conditions. Power Query evaluates these from top to bottom—the first one that evaluates to TRUE wins.

Interface showing multiple rules added to a single conditional column

Step 3: Finalizing the Calculation

You now have a multiplier column. To get the final price:

1. Select Add Column > Custom Column.
2. Enter the formula: =[Value] * [Sunday Premium].

Custom column dialog showing the multiplication formula

Verdict: This method is intuitive and reduces syntax errors. However, it becomes cumbersome when you need to check two different columns simultaneously (e.g., Day AND Product).

Method 2: The “Pro” Approach (M Code in Custom Column)

When business rules become layered—like in Scenario 2—clicking through menus becomes inefficient. Writing M code directly allows for sophisticated boolean logic.

Understanding M Language Syntax

The syntax for an IF statement in Power Query is linguistically similar to English but strictly formatted:

if [Condition] then [Result If True] else [Result If False]

The Golden Rule: M Language is Case Sensitive.

• Correct: if, then, else, and, or.
• Incorrect: IF, Then, Else, AND.
• Warning: Using IF (uppercase) will result in an immediate syntax error.

Solving Scenario 1 with Code

To replicate the Sunday surcharge without using the wizard:

1. Click Add Column > Custom Column.
2. Enter the following formula:

if [Day Name] = "Sunday" then [Value] * 1.1 else [Value]

Code editor showing a basic IF statement in Power Query

This single line of code replaces the entire process of creating a helper column and then multiplying it. It is cleaner and more efficient for the processor.

Solving Scenario 2: Complex Logic (AND / OR)

Now, let’s tackle the complex requirement:

1. Condition A: The day must be Sunday.
2. Condition B: The product is “Tiger” OR “Farmhouse Bloomer”.
3. Result: If both are true, Price 1.1 0.95.
4. Result: If only Sunday is true, Price * 1.1.
5. Else: Price remains standard.

In M code, we use lowercase and / or operators. There are two structural ways to write this.

approach A: Combined Logic Operators

We can combine the logic into a linear statement. Parentheses () are critical here to ensure the or condition is evaluated correctly within the and condition.

if [Day Name] = "Sunday" and ([Product] = "Tiger" or [Product] = "Farmhouse Bloomer")
then [Value] * 1.1 * 0.95
else if [Day Name] = "Sunday"
then [Value] * 1.1
else [Value]

Advanced M Code showing combined AND and OR logic

Approach B: Nested IF Statements

Sometimes, nesting the statements makes the logic easier for humans to read and debug. It functions like a decision tree.

if [Day Name] = "Sunday" then
    if [Product] = "Tiger" or [Product] = "Farmhouse Bloomer"
    then [Value] * 1.1 * 0.95
    else [Value] * 1.1
else [Value]

Nested IF statement structure in the Custom Column editor

Logic Breakdown:

1. Power Query first checks if it is Sunday.
2. If False: It immediately goes to the final else (returns original Value).
3. If True: It enters the second layer to check the Product name.
4. Based on the Product, it applies either the discount formula or the standard surcharge.

Conclusion

Transitioning from Excel formulas to Power Query conditional logic requires a shift in mindset. The strict requirement for lowercase syntax (if vs IF) is the most common stumbling block for beginners. However, mastering this unlocks significantly more power than standard Excel.

• Choose Conditional Columns when you need speed, simplicity, and visual confirmation for linear rules.
• Choose M Code (Custom Columns) when you face multi-layered business logic, need to reference multiple columns simultaneously, or want to keep your query steps minimal and efficient.

By adding these techniques to your toolkit, you elevate your ability to transform raw data into valuable insights. We encourage you to open a dataset and try writing your first if statement in M code today—it is the first step toward true data mastery.

References

• Excel Off The Grid – Power Query IF Statements
• Microsoft Learn – Power Query M formula language reference