Pinterest Pixel

How to Write Power Query IF Statement in Excel – 2 Easy Examples

Power Query is a new add-in introduced in Excel 2013 by Microsoft that accesses, cleans, and transforms... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to Write Power Query IF Statement in Excel - 2 Easy Examples | MyExcelOnline How to Write Power Query IF Statement in Excel - 2 Easy Examples | MyExcelOnline

Power Query is a new add-in introduced in Excel 2013 by Microsoft that accesses, cleans, and transforms all that messy data and displays it in a way that Excel loves and can work with. A particularly powerful feature is the Power Query IF statement, which enables users to test conditions and take action accordingly.

In this article, you will be covering the two methods for incorporating Power Query IF statement in Excel –

Download the Excel Workbook below to follow along and understand How to Write Power Query IF Statement in Excel –

download excel workbookPower-Query-IF-Statement.xlsx

Let’s dive into the practical applications of the Power Query IF statement.

 

Method 1 – Using Conditional Column

In Power Query, you can add a conditional column to apply a condition and determine the corresponding value. Let’s consider an example where we categorize products as “Fast Selling” or “Slow Selling” based on the quantity sold.

Suppose you have a list of products with the quantity sold listed in a data table. If the quantity sold is greater than 50, the product category will be fast selling else slow selling. Follow the steps below to add a conditional column in Power Query –

See also  Advanced Editor In Power Query

STEP 1: Highlight the data table and go to Data and select From Table/Range.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 2: In the Create Table dialog box, select OK.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 3: In the Power Query window, go to Add Column > Conditional Column.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 4: In the Add Conditional Column dialog box, name the new column “Product Category.”

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 5: Set the column name to “Quantity Sold”

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 6: Choose the operator as “is greater than”.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 7: Enter the value as 50.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 8: Specify the output as “Fast Selling”.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 9: Enter “Slow Selling” as the Else value.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 10: Finally, go to Home and select Close & Load.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

This loads the data, now with the new conditional column, back into the Excel workbook.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

 

Method 2 – Using Custom Column

Alternatively, you can write the IF statement in Power Query similarly to how you write an IF function in Excel. The syntax is as follows:

See also  Transpose Using Power Query

if condition then value1 else value2

where,

  • condition –  represents the logical test
  • value1 – result if the condition is true
  • value2 – result if the condition is false

Let us look at an example to understand better.

You can use Power Query IF statement to categorize these products based on the quantity sold using the syntax below –

= if [Quantity Sold] > 50 then “Fast Selling” else “Slow Selling”

Follow the steps below to achieve this –

STEP 1: Highlight the data table and go to Data and select From Table/Range.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 2: In the Create Table dialog box, select OK.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 3: In the Power Query window, go to Add Column > Custom Column.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 4: In the Custom Column dialog box, enter Product Category as the New column name.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 5: Enter the following in the Custom column formula –

=if [Quantity Sold] > 50 then “Fast Selling” else “Slow Selling”

See also  Import Data from CSV Using Power Query or Get & Transform

Click OK.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

The new custom column will be added in the Power Query window.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

STEP 10: Go to Home and select Close & Load.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

This will load the data with the new custom column into the Excel workbook.

How to Write Power Query IF Statement in Excel - 2 Easy Examples

 

Please note the following –

  • Power Query is case-sensitive, so you need to write “if”, “then”, and “else” in lowercase.
  • There’s no need for a comma to separate value1 and value2.
  • You can insert null if you want a result to be blank.

 

Conclusion

In a nutshell, Power Query in Excel is like a power tool for dealing with messy data. We learned two cool ways to use the IF statement – one by adding a special column based on conditions, and another by creating a custom column with a simple formula. Now, you can impress Excel with your data-handling skills!

Click here to learn more about Power Query in Excel.

If you like this Excel tip, please share it
How to Write Power Query IF Statement in Excel - 2 Easy Examples | MyExcelOnline How to Write Power Query IF Statement in Excel - 2 Easy Examples | MyExcelOnline
See also  Remove Rows Using Power Query
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...