The COALESCE Operator in M / Power Query

When performing calculations in Power Query, a common requirement is to provide a default value instead of null. One way to approach this is by using an if statement. You can for example write:

= if [Sales Amount] = null then 0 else [Sales Amount]

The Coalesce Operator

Since this is a common requirement the M-language also offers the Coalesce Operator to achieve the same. The coalesce operator is represented by ??. This operator works as a COALESCE function but technically is not a function. You can write the above expression with the coalesce operator in the following way:

= [Sales Amount] ?? 0

If we describe this in words it says: try to return the [Sales Amount] and if this value equals null then return the default value 0. Using the coalesce operator makes the code shorter and more readable.

If you want to add more columns to your formula you can add the coalesce operator multiple times. Let’s say you want to return the first value that does not equal null from the columns period 4, 3, 2 or 1 respectively. And if all values are null then return a default value of 500. You can do that by writing:

if [Sales P4] <> null then
   [Sales P4]
   else if [Sales P3] <> null then
           [Sales P3]
           else if [Sales P2] <> null then
                   [Sales P2]
                   else if [Sales P1] <> null then
                           [Sales P1]
                           else 500

The code is quite long and difficult to maintain. With the coalesce operator you can rewrite the formula to:

[Sales P4]  ??  [Sales P3]  ??  [Sales P2] ?? [Sales P1] ?? 500

This function returns the first column that does not return blank. And if all columns equal null it returns the default value of 500.

Coalesce Alternative

In case you want to get creative, there are alternative ways to replicate the coalesce function. You can use any of these to get identical results:

= List.First( List.RemoveNulls( { null, null, 1, 2 } ) )

= List.First( List.RemoveFirstN( { null, null, 1, 2 }, each _ = null ) )

= List.RemoveFirstN( { null, null, 1, 2 }, each _ = null ){0}

= List.Select( { null, null, 1, 2 }, each _ <> null ){0}

= null ?? null ?? 1 ?? 2

For learning purpose you can find those here, but in the end coalesce operator (??) is best-practice.

Under the hood the coalesce operator performs an if-condition. From a performance perspective you should not expect this code to perform any quicker than the if-statement. Yet it makes your code much shorter and more readable.

Enjoy Power Query!

Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

2 thoughts on “The COALESCE Operator in M / Power Query”

Leave a comment