Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

In this article, you learn how to use the COALESCE operator in Power Query. This special operator can help you replace missing data (‘null’ values) with something else. You’ll learn step-by-step how to do it and see examples of how it can make your data look better. Let’s get started!

What is COALESCE

The null Coalesce operator ?? allows you to return a different value instead of null. When the value on the left-hand side of the operator is null, it returns the value on the right side of the operator. It works like the ISNULL function in SQL. So when is this useful?

When performing calculations, a common requirement is to provide a default value instead of null. Null is a special case that represents the absence of a value and is different from zero.

The tricky part is that some operators don’t know how to work with these values. For example, 5 + null = null, whereas you may have expected 5. For this reason (and others), it’s useful to know how to work with null.

One way to instruct what to do with null values is by using an if statement. You can, for example, write:

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

In cases where Power Query finds a null it then returns zero instead. Yet there are easier ways.

The Coalesce Operator

Since handling nulls 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
Elaborate if statement instead of Coalesce

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
Statement with Coalesce Operator in Power Query

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 purposes, 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.

Conclusion

So that’s it! The COALESCE operator is a super useful tool to have in your Power Query toolbox. It can save you a lot of time and hassle when you’re working with data that has a lot of null values. I hope you found this article helpful and that you’re able to put this operator to good use in your own data projects.

If you’re interested in other ways to shorten your code, make sure to check out this article that shows how to mimic an IN operator (multiple OR statements).

Enjoy Power Query!

Share on:

Latest from my blog

  1. This is Awesome! Thanks Rick. I was just writing a long winded ‘if null’, etc statement then thought “I wish M had a Coalsce function”
    Ta Da! You came to the rescue.

    Reply
  2. = if [Sales Amount] = null then 0 else [Sales Amount]

    No matter how much I type, the port comes out as null.
    No attachments? There are no files here so I can’t practice.

    Reply
    • Hi there. Please make sure your column is recognized as a number, and not a text value. In case your null value is recognized as text, your formula can include [Sales Amount] = “null”.

      However as a number I don’t recognize the issue. Give below code a try and see what happens:

      let
          Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjBUitUBUmDSGMLJK83JATNMDJD4sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Amount" = _t]),
          #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Amount", type number}}),
          #"Added Custom" = Table.AddColumn(#"Changed Type", "Test", each if [Sales Amount] = null then 0 else [Sales Amount])
      in
          #"Added Custom"
      Reply
  3. Hi Rick,
    I like the presented approach and also the code readability, but I realized that there is a downside of that approach. I am currently connecting to a S4 Hana database and want to perform a coalesce for 4 columns (and do not want to do in SQL due to maintainability reasons for the users).

    Col4 ?? Col3 ?? Col2 ?? Col1 ?? 0 would be really great, but this will not be performed on the source server, but on the Power BI server in a DataFlow. The only way, I could make Power Query performing that in the source server was via the if – else statement. 🙈

    Reply
    • Unfortunately that’s how it is to date. The if-statement folds, the COALESCE not always. Until further optimizations from Microsoft, this is the situation.

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.