As an analyst, dividing data into multiple groups is one of the common tasks. It helps in analyzing the data with an additional dimension.
Modern Excel has multiple alternatives for adding nested if. In the previous blog, we have seen how to use native Excel formulas IFS, LOOKUP, XLOOKUP to achieve this. In this blog, we use Power Query to get the desired output.
Consider the sample data below:
The objective is to include a column with the following categories based on the Order Quantity:
Step 1: Importing data table into Power Query
Select the data table > DATA > From Table/Range
Power Query Editor window:
Adding the Conditional Column
Add Column > Conditional Column
Conditional Column Input window:
Define the Column Name and add conditions. Click on Add Clause to add more conditions
Question: How many “Else If” or “Add Clause” we need to include?
The thumb rule is: if the number of categories equals n, then we require (n-1) “Else If” or “Add Clause”. In this case, we have five categories; hence we need four “Else If”, and the last class comes under “Else”.
Condition Column has an inbuilt AND statement in each clause. It reads the first clause, and move to the next clause after satisfying the first condition. So, in this case, the second clause checks for the values greater than and equal to 10 AND less than 20 then assigns the category “b/w 10 & 20” if it meets the criteria.
Click “OK”, and that is it! We have an additional column with the required categories.
Loading the table to Excel
Go to Home > Close & Load To > Table
Power Query adds the transformed table into a new sheet:
Editing/Updating Conditional Column
The best feature about Power Query conditional column is that modifying the condition is super easy.
Select any cell within the Power Query table > Query > Edit
Click on the gear icon next to the steps under “Applied Steps” to open the Conditional Column option
Make relevant changes > Click Ok > Close & Load
Power Query updates the table with the recent changes.
Benefits of using Power Query:
· No need of writing a formula
· Editing the formula is simple
· It considers any changes in the source data.