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 column. 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
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.
Step 2: Add Conditional Column
After you select the column from your data table, you can add conditional column with the help of few clicks as given below-
Add Column > Conditional Column
Step 3: Define Conditions in Add Conditional Column 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 as Silver.
- The second condition means that customers whose OrderQty is less than or equal to 100 will be called as Gold Customers.
- The third condition states that the remaining values in OrderQty which do not meet the first two conditions will be called as Platinum.
To add the rules you have to click on Add rule button. At last, click on OK.
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.
You can learn more: