Coalesce Operator in Power Query

You can use the Coalesce operator (??) in Power Query to return the first non-null value from a list of alternatives.

This is especially helpful when your data contains many null values, and you want to replace them with non-null fallback values.

Coalesce Operator Syntax

Below is the syntax of using the QLS operator in Power Query.

value1 ?? value2 ?? value3

In the above scenario, our query is going to go from left to right, and it is going to give you the first non-null value.

If all values in the coalesce chain are null, then the coalesce operator returns null.

Also read: Remove Null Values in Power Query

Coalesce Operator Example

Let me show you an example of how to use the Coalesce operator in Power Query.

Suppose you have the Excel table below with customer email contacts. Some fields are empty.

A dataset we use in Power Query to demonstrate a coalesce operator.

You want to create a new column called FinalEmail that displays the first available email for each customer based on the following priority:

PrimaryEmail first, then SecondaryEmail, and finally BackupEmail.

Here’s how to do it using the Coalesce operator in Power Query:

  1. Select any cell in the Excel table.
  2. Open the Data tab and click the From Table/Range option on the Get & Transform Data group.
Open the data in Power Query Editor by clicking on the 'from Table Range' button.

The above step loads the table data into Power Query Editor.

  1. On the Power Query Editor, open the Add Column tab and click the Custom Column option on the General group.
Click on Custom Columns option in the ribbon.

The above step opens the Custom Column dialog box.

  1. On the Custom Column dialog box, do the following:
  • Type ‘FinalEmail’ on the ‘New column name’ box.
  • Place the cursor after the equal sign on the ‘Custom column formula’ box and enter the formula 
=each [PrimaryEmail] ?? [SecondaryEmail] ?? [BackupEmail].' 
  • Click OK.
Enter the formula and custom column.

Note: The formula ‘each [PrimaryEmail] ?? [SecondaryEmail] ?? [BackupEmail]’ tells Power Query to use the value from the ‘PrimaryEmail’ column. If that value is null, it falls back to the ‘SecondaryEmail’ column. If that’s also null, it then falls back to the ‘BackupEmail’ column.

The above step adds a FinalEmail custom column to the query, displaying the first available email for each customer from the PrimaryEmail, SecondaryEmail, and BackupEmail columns.

The result gives you the value of e-mail using the coalesce operator.
  1. Select PrimaryEmail, SecondaryEmail, and BackupEmail columns and remove them.
Remove the columns you don't need.
  1. Open the Home tab and click the Close & Load command button on the Close group.
Click on the 'Close and Load' button.

The query data is loaded into Excel.

The query is now loaded into Excel.

I have shown you how to use the Coalesce operator in Power Query. I hope you found the tutorial helpful.

Other Power Query articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.