Power Query IF ELSE and IF AND statements

Power Query IF ELSE and IF AND statements

Power Query IF ELSE example

Here’s a simple IF then ELSE example in Power Query, splitting a list of values into two categories. We’ve got a list of ages and values, and want to categorise the age values as either ‘Adult’ (age 18 and above) or ‘Child’.

= if [Age] > 17 then "Adult" else "Child"

Showing values split into two categories based on an IF ELSE statement
We’ve also used conditional formatting to highlight one of the categories.

Create an IF AND … ELSE statement in Power Query and Power BI

To create nested IF statements in Power Query, we chain a series of ‘if … then … else’ statements.

To create an IF AND statement to combine conditions, we simply add ‘and’ between each condition. For example, we might want to group one variable based on criteria in two other variables, or two conditions (such as greater than X and less than Y).

Power Query IF AND ELSE syntax

if [condition_1] and [condition_2] then [do this]

else if [condition_3] and [condition_4] then [do that]

else [do the other]

Lightbulb icon demonstrating good idea, used to highlight solution in excelquick.com website As with nesting any IF…ELSE statements, we need to make sure that the order of the statements is set up correctly – see our post about nesting IF statements here with important rules and tips https://excelquick.com/excel-formula/nested-if-statement-excel/2/#key-if External link icon. The statements are processed in the order presented to Power Query, so the statements need to be in the right order to be evaluated correctly.

Power Query if and then else statements
The results

Pages: 1 2