IF Function in Power Query

The “if” function is used similarly to the “if” function used in other programming languages. Here also we have two expressions one evaluated when the If condition evaluates to true and one evaluated when if condition evaluates to false.

Syntax

if "condition" then "value-if-true" else "value-if-false"

This can be broken down into three parts:

condition: This is the condition for the if function to check and it must return a logical value i.e. either true or false, otherwise error will raise.
value-if-true: This is the result or action we want when the condition evaluates to true.
value-if-false: This is the result or action we want when the condition evaluates to false.

Tip Remember, Power Query is case-sensitive, so use lowercase for “if”, “then”, and “else”.

Example: The function evaluates on the “Marks” column and returns the result “Pass” or “Fail”. Using the Table.AddColumn function we have created a column with name “Status”.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Marks = 568], 
      [CustomerID = 2, Name = "Katrina", Marks = 855], 
      [CustomerID = 3, Name = "Alia", Marks = 367], 
      [CustomerID = 4, Name = "Vicky", Marks = 458], 
      [CustomerID = 5, Name = "Mohini", Marks = 278], 
      [CustomerID = 6, Name = "Meenakshi", Marks = 289], 
      [CustomerID = 7, Name = "Esha", Marks = 875], 
      [CustomerID = 8, Name = "Anjali", Marks = 380]
    }
  ), 
  Return = Table.AddColumn(MyTable, "Status", each if [Marks] > 500 then "Pass" else "Fail")
in
  Return

The output of the above code is shown below:

Previous Next