*
en | de
All Content
Timespan
explore our new search
​
How to write a Multiple (nested) IF in Power Query
Power BI
May 5, 2023 3:05 PM

How to write a Multiple (nested) IF in Power Query

by HubSite 365 about Wyn Hopkins [MVP]

Microsoft MVP | Author | Speaker | Power BI & Excel Developer & Instructor | Power Query & XLOOKUP | Purpose: Making life easier for people & improving the quality of information for decision makers

Data AnalyticsPower BILearning Selection

I show 2 methods to write a multiple IF formula in Power Query including using an "Approximate Match"

I show 2 methods to write a multiple IF formula in Power Query including using an "Approximate Match" method

00:00 Intro

00:34 The Setup and Goal

01:24 A better way of writing a nested IF

06:54 How to do an Approximate Match in Power Query

How to write a Multiple (nested) IF in Power Query

A multiple (nested) IF in Power Query is a type of conditional expression used to evaluate a list of conditions and return a value when a condition is met. It is often used to analyze complex data sets or to make decisions based on multiple criteria.

To write a multiple IF statement in Power Query, you will need to nest multiple IF statements within one another. The IF statement is used to evaluate a condition and returns a specific value depending on the outcome of the evaluation. You can nest multiple IF statements so that the result of one IF statement is used as the condition of the next IF statement.

For example, if you want to evaluate whether a person is a senior, adult, or child based on their age, you could use the following multiple (nested) IF statement:

IF [Age] > 65 THEN "Senior"

ELSEIF [Age] > 18 THEN "Adult"

ELSE "Child"

In this example, the first IF statement evaluates if the age is greater than 65 and if it is, it returns the value "Senior". If the age is not greater than 65, the ELSEIF statement is evaluated and if the age is greater than 18, the value "Adult" is returned. If the age is not greater than 18, the ELSE statement is evaluated and the value "Child" is returned.

You can also use the AND and OR operators in Power Query to combine multiple conditions in a single IF statement. The AND operator will return TRUE only if all conditions are TRUE and the OR operator will return TRUE if any of the conditions are TRUE.

For example, if you want to evaluate whether a person is an adult or senior based on their age and gender, you could use the following multiple (nested) IF statement:

IF [Age] > 65 AND [Gender] = "Male" THEN "Senior"

ELSEIF [Age] > 65 AND [Gender] = "Female" THEN "Senior"

ELSEIF [Age] > 18 THEN "Adult"

ELSE "Child"

In this example, the first IF statement evaluates if the age is greater than 65 and the gender is "Male". If both conditions are TRUE, the value "Senior" is returned. Similarly, the second IF statement evaluates if the age is greater than 65 and the gender is "Female". If both conditions are TRUE, the value "Senior" is returned. If neither of the first two conditions are TRUE, the ELSEIF statement is evaluated and if the age is greater than 18, the value "Adult" is returned. If the age is not greater than 18, the ELSE statement is evaluated and the value "Child" is returned.

Writing multiple (nested) IF statements in Power Query can be a powerful tool for analyzing complex data sets and making decisions based on multiple criteria.

Oct 10, 2018 — I would like to create a new column in Power Query that identifies the fiscal month a customer wants his/her order. If Customer1 wants his product on January 5 ...

Writing an IF Function in Power Query ... We will to add a new column to the table, so click the Add Column tab of the Ribbon. In Power Query there is a ...