NEW! FREE 15 Hours Microsoft Excel Course! View now 

Power BITutorials

How to Add Conditional Column in Power Query

2 Mins read
ADD CONDITIONAL COLUMN

How to Add Conditional Column in Power Query Step-by-Step.

[toc]

In Power Query, there is a feature to add a new column called Conditional Column feature. You can define IF-THEN-ELSE conditions to add the conditional columns. When the conditions are fulfilled, the conditional column will automatically demonstrate the values that you have specified using IF-THEN-ELSE conditions.

Add Conditional Column Using Power Query Step-by-Step

Let’s take an example to understand this concept. Here, we have a data table showing the details about the quantity ordered by customers. Suppose, we want to categorize these customers w.r.t their orders like silver, gold, and platinum. We can easily do this using Add Conditional Column feature in Power Query. This is explained below using a few steps-

Step 1: Select the column to apply conditions

When you load the data in Power Query, select the column on which you want to apply the conditions. In this case, we have selected the OrderQty column as shown below.

BEFORE ADDING CONDITIONAL COLUMN

Step 2: Add Conditional Column

After you select the column from your data table, you can add conditional columns with the help of few clicks as given below-

Add Column  >  Conditional Column

ADD CONDITIONAL COLUMN

[xyz-ihs snippet=”Data-Analyst-Course”]

 

Step 3: Define Conditions in Add Conditional Columns Window

After you click on Conditional Column option, a window gets open where you have to specify the conditions for adding a new column to your data table.

First, you have to specify the name of the column as we have specified here ‘Customer Category’.

We have applied three conditions in this case. They are as follows-

    • The first condition means that customers whose OrderQty is less than or equal to 50 will be called Silver.
    • The second condition means that customers whose OrderQty is less than or equal to 100 will be called Gold Customers.
    • The third condition states that the remaining values in OrderQty which do not meet the first two conditions will be called Platinum.

To add the rules you have to click on Add rule button. At last, click on OK.

DEFINE IF-THEN-ELSE CONDITIONS IN CONDITIONAL COLUMN WINDOW

Step 4: Close & Apply

Now, you will see that one new column is added to your data table as shown below that meets all the conditions you applied. After you get the desired output to click on Close & Apply button to apply all the pending changes.

DATA TABLE AFTER ADDING CONDITIONAL COLUMN

You can learn more:

Related posts
Power BITutorials

Opportunity Analysis Power BI Dashboard

1 Mins read
Information: A software company that has two sales channels: direct and partner. The sales manager created this dashboard to track opportunities and…
Power BITutorials

Procurement Analysis Power BI Dashboard

1 Mins read
Information: It analyzes a manufacturing company’s spending on vendors by category and location. we explore these areas: Who the top vendors are…
Power BITutorials

Retail Analysis Power BI Dashboard

1 Mins read
Information: It analyzes retail sales data of items sold across multiple stores and districts The metrics compare this year’s performance to last…