Create Custom Column Using IF Statement in Power Query [2 Examples]

IF statements allow users to add custom columns to their datasets using Power Query, an effective data transformation tool. Using this method, you may use the Power Query Editor to perform customized data manipulation and analysis using dynamic conditions to create data in a new column. In this article, I’ll guide you through creating a custom column using the IF statement in Power Query.

What is the Custom Column Feature in Power Query?

The Custom Column feature in Power Query is like a magic wand for your data. It lets you create your own special columns with specific rules that you decide. Imagine you have a bunch of data, and you want to add a new column that does something special, like calculating grades or marking ‘Pass’ or ‘Fail.‘ With Custom Column, you can easily create these unique columns using simple formulas, making your data work exactly the way you want it to. It’s like giving your data superpowers to make it more useful and interesting for your needs!

Example 1: Using IF Statement to Create a New Custom Column in Power Query

In this example, we’ve got a dataset with students’ names in the “Name” column and their marks in the “Marks” column. Now, let’s spice it up by adding a new column called “Remarks” using the custom column feature. In this nifty new column, if the marks are equal to or greater than 40, we’ll mark it as “Pass”; otherwise, it’s a friendly “Fail.” The dataset is given below:

Follow these simple steps to create a custom column using IF statements:

Step 1: Open the Power Query Editor from Table/Range

1. Select the table.
2. Open the Excel ribbon and select the “Data” tab.
3. Click “Get Data,” then select “From Table/Range.”
4. Turned on “My Table has a Header.”
5. When the Power Query Editor opens, your data will be visible.

Step 2:  Apply Custom Column to Create a New Column

1. Select the table that contains the “Marks” column.
2. Go to the “Add Column” tab.
3. Click on “Custom Column.”
4. In the “Custom Column” dialog, enter “Remarks” as the New column name.
5. Copy the code: = if [Marks] >= 40 then “Pass” else “Fail”
6. Enter the above IF statement in the “Custom formula” box.
7. Click OK. A new column named “Remarks” will be added to your table.
8. Click “Close & Apply” to close the Power Query Editor and load the modified table back into Excel.

Example 2: Create a New Custom Column in Power Query Using Nested IF Statement

To assign grades to numbers in our dataset, you can utilize a nested IF statement to create a new “Remarks” column. This neat trick helps display a specific grade corresponding to each mark range.

Follow these steps to easily create a new column using a nested IF statement:

1. Open the Power Query Editor.
2. Choose the table where the “Marks” column is located.
3. Go to the “Add Column” tab.
4. Select “Custom Column.”
5. In the “Custom Column” dialog, enter “Remarks” as the New column name.
6. Copy the code:

   if [Marks] >= 90 then "A+"
   else if [Marks] >= 80 then "A"
   else if [Marks] >= 70 then "B"
   else if [Marks] >= 60 then "C"
   else if [Marks] >= 50 then "D"
   else if [Marks] >= 40 then "E"
    else "F"

7. Enter the above nested IF statement in the “Custom formula” box.
8. Press OK. To your table, a new column called “Remarks” will be added.

Conclusion

In this example, I demonstrate how to craft a custom column in Power Query using IF and Nested IF statements. I’ve applied this technique to a student marksheet dataset, but feel free to adapt it for similar scenarios in your data.

Frequently Asked Questions

How do you write an if statement in the Power Query custom column?

To write an IF statement in a Power Query custom column, follow these steps:

1. Open Power Query Editor.
2. Select the table.
3. Go to the “Add Column” tab.
4. Click “Custom Column.”
5. Enter a column name.
6. In the formula box, use the syntax: = if [YourColumn] = “YourCondition” then “ResultIfTrue” else “ResultIfFalse”
7. Replace “YourColumn” with the actual column name, and set your conditions and results.
8. Click OK to apply.

Can I use multiple conditions in a single IF statement in Power Query?

Yes, you can nest multiple conditions using the IF statement, for example: = if [Column1] > 50 then “High” else if [Column1] > 30 then “Medium” else “Low”

What are some common mistakes when writing IF statements in Power Query?

The common mistakes are given below:

1. Incorrect Column Names.
2. Missing Quotation Marks.
3. Mismatched Parentheses.
4. Case Sensitivity.
5. Handling Null Values.
6. Logical Operators.
7. Data Type Mismatch.