How to Add a Conditional Column in Power Query in Excel?

We may use Power Query to generate new columns whose values depend on one or more criteria that have been applied to existing columns in your database. The condition imposed on an existing column in the data model serves as the foundation for Power BI's conditional column. On the Add column tab, in the General category, is the Conditional column command. In this article, we are going to discuss the Conditional column in Power Query and in Power Pivot queries.

Step By Step Implementation

Step 1: Create an excel table with fields Roll No., Student name, and Marks.

Step 2: Select a table. Then navigate to the data tab on the top of the ribbon and select the Get Data option and then go to from other sources and choose from Table/range.

Step 3: Then create a Table dialog box appear. Here select the range and check my table has headers then click on OK.

Step 4: Now as you can see the excel table is converted to a power query editor.

Step 5: Now in the power query go to the add column tab on the top of the ribbon and here select the conditional column.

Step 6: Add Conditional Column dialog box appears. Here write the new column name and then apply the if condition.

Step 7: Here in the New column name we gave a column name Pass/Fail. Then in the If condition in column name selects Marks, in Operator Select is greater than, in value write 40 and in the output write Pass. Now click on Add clause.

Step 8: Now in the Else If condition in column name selects Marks, in Operator Select is less than, in value write 40 and in the output write Fail and then click OK.

Step 9: Now as you can see new column is created with Pass/Fail name. Here it shows the students whose marks are less than 40 fail and the students whose marks are more than 40 Pass.