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.

conditional column between values in power bi

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.

conditional column power bi

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.

conditional column in power query

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

power bi conditional column

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

add conditional column power bi

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.

what is conditional column in power bi

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

add conditional column in power bi

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:

power bi conditional column between values

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.

power query conditional column between values

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’.

See also  Power BI DAX Min Date Sum

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’.

conditional column between values in power bi using power Query

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

conditional column between values Power BI

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.

power bi conditional column multiple conditions

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.

Multiple conditions for a conditional column in Power Query

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.

power query conditional column multiple conditions

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

power bi add conditional column multiple conditions

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.

power bi conditional column using dax

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.

Conditional columns using DAX in Power BI

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

Conditional Calculated Column in Power BI DaX

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.
Conditional Column using Power BI DAX

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

Microsoft power bi conditional column using DAX

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

See also  Power BI Conditional Formatting Based On Field Value

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
conditional column based on two columns in Power BI

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.

Power BI conditional column based on two column

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

Power BI Conditional Column based on 2 columns

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”.
How to Add Conditional Columns In Power BI

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

conditional column based on two columns in Microsoft Power BI

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

See also  Power BI Slicer Multiple Columns

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.

Power Query Conditional Column IF Blank

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.

power bi conditional column if and

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

conditional columns power bi

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.

power query conditional column multiple criteria

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

Microsoft power bi conditional column blank using Power Query

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:

power bi conditional column null
  • 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”.
Microsoft power bi conditional column null

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

conditional column power bi null

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

Some more articles you may like:

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.

>