How to Use Conditional Column in Power Query in Power BI

In Power BI, use the Conditional Column feature in Power Query Editor to create a new column that changes values based on conditions you set. You can add conditions using simple rules like if-then-else without writing code, making data transformation easy and visual.

Syntax

The Conditional Column in Power Query uses a simple if-then-else logic to assign values based on conditions.

The general pattern is:

• if [Column] operator value then result1
• else if [Column] operator value then result2
• else default_result

Each condition checks a column value and returns a result if true. If none match, it returns the default.

powerquery

if [Sales] > 1000 then "High" else if [Sales] > 500 then "Medium" else "Low"

Example

This example creates a conditional column named Sales Category based on the Sales column values:

• If sales are greater than 1000, label as "High"
• If sales are between 501 and 1000, label as "Medium"
• Otherwise, label as "Low"

powerquery

let
    Source = Table.FromRecords({
        [Product="A", Sales=1200],
        [Product="B", Sales=800],
        [Product="C", Sales=300]
    }),
    AddedConditionalColumn = Table.AddColumn(Source, "Sales Category", each if [Sales] > 1000 then "High" else if [Sales] > 500 then "Medium" else "Low")
in
    AddedConditionalColumn

Output

Product | Sales | Sales Category
--------|-------|--------------
A       | 1200  | High
B       | 800   | Medium
C       | 300   | Low

Common Pitfalls

Common mistakes when using conditional columns include:

• Not covering all possible cases, which leads to unexpected null values.
• Using incorrect column names or syntax errors in conditions.
• Mixing data types in conditions, like comparing text to numbers.

Always double-check your conditions and test with sample data.

powerquery

/* Wrong: Missing else clause leads to null values */
if [Sales] > 1000 then "High" else if [Sales] > 500 then "Medium"

/* Right: Add else clause to cover all cases */
if [Sales] > 1000 then "High" else if [Sales] > 500 then "Medium" else "Low"

Quick Reference

Step	Description
Open Power Query Editor	Click 'Transform Data' in Power BI Desktop
Select Add Column tab	Choose 'Conditional Column' button
Define conditions	Set column, operator, value, and output for each rule
Add else clause	Set default output if no conditions match
Click OK	Apply the conditional column to your data

Key Takeaways

Use the Conditional Column feature in Power Query Editor to create if-then-else logic without coding.

Always include an else clause to handle all data cases and avoid null results.

Check column names and data types carefully to prevent errors in conditions.

Test your conditional column with sample data to ensure it works as expected.