How to Add Conditional Column in Power BI [Using Power Query]

Let’s say you need to add a new column to a large Power BI table depending on certain criteria. In this case, the conditional column option in Power BI is really helpful as it significantly simplifies the work.

In this Power BI tutorial, I will explain how to add Conditional column in Power BI and the Conditional Column in Power Query. Also, we will discuss the topics below:

• Power BI conditional column between values
• Power BI conditional column multiple conditions
• Power BI DAX conditional column
• Power BI conditional column based on two columns
• Power query conditional column if blank
• Power BI conditional column null

Conditional Column in Power BI

The Power BI Conditional column allows you to create a new column in your dataset based on specific conditions. In Power BI, we can create a conditional column using Power Query Editor.

For example, you could tell Power BI, “If this condition is met, put this value in the new column; if not, put a different value.

How to Add Conditional Column in Power Query

Let’s say you have a dataset containing information about products, their prices, and the quantity sold.

Now follow the below steps on how to add a conditional column in Power BI:

1. Open Power BI Desktop and load the data set. Then, in the Data pane, you can see your data set.

2. Then, under the Home tab, click Transform data.

3. In the Power Query Editor, click Conditional Column under the Add Column tab.

4. Provide the new column name as “Conditional Column.” Next, select the column name as “Total Sales,” choose the operator “is greater than,” set the value as “5000,” and the output as “High.”

Then, click on “Add clause.” For the ElseIf part, select the column name as “Total Sales,” the operator as “is less than,” set the value as “3000,” and the output as “Low.”

Finally, in the Else part, enter “Medium.” Click on “OK” to apply the changes.

5. Then, you can see a new column added in the Power Query editor.

This way, you can add a conditional column in Power BI.

Power BI Conditional Column Between Values

Let’s see how to create a conditional column between values in Power BI.

For this example, we will create a conditional column that checks the Aging column. If the aging value is less than 3, it’ll be labeled as ‘Fast delivery.’ If it’s greater than 3 and less than 7, it’ll be labeled as ‘On-time delivery.’ Otherwise, it’ll be labeled as ‘Late Delivery.’

In this example, we will use below table:

Follow these steps to create the conditional column in the Power Query editor:

1. In the Power Query Editor, click Conditional Column under the Add Column tab.

2. Provide the new column name as ‘Status.’ Then, select the column name as ‘Aging’, choose the operator as ‘less than or equal to’, set the value as ‘3’, and the output as ‘Fast Delivery’.

Click ‘Add clause’. For the ElseIf part, select the column name ‘Aging,’ choose the operator ‘less than or equal to,’ set the value to ‘7’, and output ‘On-time Delivery.’

Finally, for the Else part, enter ‘Late Delivery.’ Click ‘OK’.

3. You’ll notice the conditional column with values added to the table in Power BI now.

This way, you can create a conditional column between values in Power BI.

Power BI Conditional Column Multiple Conditions

Let’s see how to create Conditional Column Multiple Conditions in Power BI.

Imagine you’re running a small retail store. You want to categorize your customers based on their purchase behavior to offer them targeted promotions.

You have a dataset with customer information, including their age, gender, and total spending.

You want to create a new column in your Power BI dataset that categorizes customers into three groups: “High Spenders,” “Regular Spenders,” and “Low Spenders.”

Now follow the below steps to do this:

1. In the Power Query Editor, click Conditional Column under the Add Column tab.

2. Provide the new column name as “Conditional Column.” Next, select the column name as “Total Spending,” choose the operator “is greater than,” set the value as “500,” and the output as “High Spenders.”

Then, click on “Add clause.” For the ElseIf part, select the column name as “Total Spending,” the operator as “is less than,” set the value as “100,” and the output as “Low Spenders.”

Finally, in the Else part, enter “Regular Spenders.” Click on “OK” to apply the changes.

3. Now you can see we have added the conditional column to the table using Power Query in Power BI.

This way, you can create Conditional Column Multiple Conditions in Power BI.

Power BI Conditional Column using DAX

Let’s see an example of a Power BI conditional column using DAX.

Imagine you have a dataset that includes the price of each product and the Product Name available.

You want to create a conditional column that categorizes your products into different price range groups, such as “Low Price,” “Medium Price,” and “High Price.”

To do this, follow the below steps;

1. Open Power BI Desktop and load the data set. Then, in the Data pane, you can see your data set.

2. Go to the Table view; under the Table tools, click the New column.

3. In the formula bar, put below the DAX expression. Then click the Commit button.

Price Range = 
SWITCH(
    TRUE(),
    Products[Price] < 10, "Low Price",
    Products[Price] >= 10 && Products[Price] <= 50, "Medium Price",
    Products[Price] > 50, "High Price",
    "Other"
)

Where:

• Price Range = This line defines the calculated column’s name that will display each product’s price range.
• SWITCH() = This is a DAX function that evaluates a series of conditions and returns a value based on the first condition that is met.
• TRUE() = This condition always evaluates to true, so it triggers the SWITCH function to start evaluating the subsequent conditions.
• Products[Price] < 10, “Low Price” = If the price of a product is less than 10, it assigns the label “Low Price” to that product.
• Products[Price] >= 10 && Products[Price] <= 50, “Medium Price” = If the price of a product is between 10 and 50 (inclusive), it assigns the label “Medium Price” to that product.
• Products[Price] > 50, “High Price” = If the price of a product is greater than 50, it assigns the label “High Price” to that product.
• “Other” = If none of the above conditions are met, it assigns the label “Other” to the product.

4. Then, you can see a new column added in the Table view.

This way, you can add a Power BI conditional column using DAX.

Power BI Conditional Column Based on Two Columns

Now we see how to create conditional columns based on two columns in Power BI.

Imagine you’re running a small retail store selling electronics, and you want to track the popularity of certain products based on their price and customer ratings.

I have a SharePoint list named Sales Data with two different columns:

• Product – Single line of text
• Price – Currency
• Customer Ratings – Number

Let’s say you want to create a new column called “Product Popularity” based on the Price and Customer Ratings columns. You want to categorize products as either “Highly Popular,” “Moderately Popular,” or “Less Popular” based on the following criteria:

• If the Price is high (above 500) and the Customer Ratings are high (above 4.5 stars), classify the product as “Highly Popular.”
• If the Price is moderate (between 200 and 500) and the Customer Ratings are above 4 stars, classify the product as “Moderately Popular.”
• If the Price is low (below 200) or the Customer Ratings are below 4 stars, classify the product as “Less Popular.”

To do this, follow the below steps:

1. Open Power BI Desktop and load the data. Then, go to the Power Query Editor.

2. Then, under the Add Column tab, click Custom Column.

3. Then the Custom column window will open, and provide the column name, then provide the formula in the Formula box. Then click OK.

if [Price] > 500 and [Customer Ratings] > 4.5 then "Highly Popular" 

else if [Price] >= 200 and [Price] <= 500 and [Customer Ratings] > 4 then "Moderately Popular" 

else "Less Popular"

This IF statement Tells:

• If the price is over $500 and the customer ratings are higher than 4.5, then the item is labeled as “Highly Popular”.
• If the price is between $200 and $500 (inclusive) and the customer ratings are greater than 4, then the item is labeled as “Moderately Popular”.
• If the item doesn’t meet the conditions specified in the previous two statements, then it’s labeled as “Less Popular”.

4. Then, you can see a new column added in the Power Query Editor.

This way, you can create conditional columns based on two columns in Power BI.

Power Query Conditional Column IF Blank

Let’s see an example of a Power BI conditional column blank.

Imagine you’re managing a small online store selling gadgets. You have a database with customer orders, including their shipping addresses. However, sometimes, customers forget to fill in their apartment number in the address field.

Now, you want to create a report using Power BI that categorizes orders based on whether they have an apartment number. If no apartment number is provided, you won’t have to fill in the blank as “No Apartment.”

To do this, follow the below steps:

1. Open Power BI Desktop and load the data. Then, go to the Power Query Editor.

2. In the Power Query Editor, click Conditional Column under the Add Column tab.

3. Provide the new column name as “New Apartment Number.” Next, select the column name as “Apartment Number,” choose the operator “equals,” do not set the value,” and the output as “No Apartment.”

Finally, in the Else part, select Apartment Number” Click on “OK” to apply the changes.

4. Now you can see the condition column is added to the table for blank value using Power Query in Power BI.

This way, you can create a Power BI conditional column blank.

Power BI Conditional Column null

In this example, you can see how to manage a Power BI conditional column without a value.

For this example, we use below table:

• In Power Query Editor, go to the “Add column” tab, then click on “Conditional column” from the ribbon.
• Next, the Conditional Column window will open. Provide the column name as needed. In the “if” part, choose the Aging column, select the operator as “equals,” set the value as “null,” and the output as “No Status.” Click on the “Add clause” button. In the “else if” part, choose the column name as Aging, select the operator as “less than or equal to,” set the value as 3, and the output as ‘Fast Delivery’.
• After that, click on “Add clause.” Next to the “Else If,” select the column name Aging, choose the operator as “less than or equal to,” set the value as 7, and the output as ‘On-time Delivery.’
• Then, in the “Else” part, type ‘Late Delivery.’ Finally, click on “OK”.

Then, you can see a new column added in the Power Query Editor.

This way, you can manage a Power BI conditional column null value.

Some more articles you may like:

• Create Date Hierarchy in Power BI
• Power BI Clustered Column Chart
• Power BI Color Based On Value
• Power BI Count Group by
• Power BI Date Slicer
• Power Query Date Functions

We’ve explored the concept of conditional columns in Power BI, which helps us make decisions based on certain conditions.

We’ve learned how to create conditional columns in various scenarios, such as between values with multiple conditions, using DAX expressions based on two columns, handling blank values, and dealing with null values.