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.

Trying to sum columns with null values in Power Query but getting unexpected results? It is something beginners bump into all the time. And the reason for this is because null values have very particular behavior.

This article that goes into the details of null values, starting with the basic characteristics and by the end of this article you know 3 ways on how you can SUM null values in Power Query.

If you would rather watch the Video Tutorial, make make sure to check out the video at the start of this page.

1. Understanding null values in Power Query

There are some intricacies to handling null values in Power Query, let’s break it down very simple at first: null values indicate an empty cell or the absence of any data.

And in that sense, you can’t really call null a value. While zero is an actual value, null is the absence of a value. And these null values have some funky characteristics.

The first thing to note is that a null value in a formula is not a zero. And a null value is not equal to a blank value.

null = null   = true
null = 0      = false
null <> null  = false
null = ""     = false

Let’s take a moment to look at comparing null values in Power Query. When comparing null to most operators, the result will be null.

null < 1      = null
8 + null      = null
2 * null      = null
4 / null      = null
"abc" & null  = null

It’s good to understand this behavior thoroughly. When you were writing your code you may have wanted it to perform if null then 0.

2. How to Sum Null Values

With this in mind we come to the issue many people experience in Power Query. You want to sum column values ignoring null values, or treating null values as zero. Let’s imagine the following picture.

Sum null values in Power Query

The dataset shows 10 products, the quantity sold, unit price and if available the taxes for each of the products. As a user you want to calculate the total sales per product.

Multiplying Quantity by Unit price is easy and causes no problems. Yet when adding the Sales Tax some rows contain null values. And as shown in previous example, adding null always results in null.

Most of us in that situation want the null to be treated as 0. I will now show you 3 ways on how to sum columns with null values, treating them as zero.

2.1. Replace Null by 0

One way to get the desired result is to replace null values with zeros. When you convert null to zero, you don’t have to deal with null values anymore. To do that:

  • Right click on the desired column -> click Replace values -> Replace null by 0.
User Interface showing how to replace null by 0

2.2. Check Null with If Statement

Another way is to do error checking before you sum null values. Using a conditional if-statement you can check if a column contains a null value.

If the result is null then you can return 0 or you could decide to not take that column into account. Your formula can exclude the column with the null value by writing:

Custom column to catch null through error checking
= if [Tax Amount = null then 
     [Quantity] * [Unit Price] else
     [Quantity] * [Unit Price] + [Tax Amount]

Before solution may work well when working with a low amount of columns, but gets rather arbitrary when the amount increases. You would have to catch the null values for each of the columns. There is a better solution.

2.3. Use the function List.Sum

A more robust way to add null values is to use the List.Sum function in Power Query. This function returns the sum of the non-null values in the provided list. And if there are no non-null values in the list, it returns null.

To provide a list in Power Query you always have to surround the provided values with curly brackets. What’s good to know is that a single value in a list can also be a calculation.

So the first value in the list could multiply Quantity by Unit Price while the second value is the Tax Amount as shown in below picture.

List.Sum function to sum null values in a custom column
= List.Sum( 
     { [Quantity] * [Unit Price],
       [Tax Amount] 
     }
  )

In this way you multiply several components, while still making use of the List.Sum functionality. And when new columns are added, it’s also very easy to adjust the formula.

You are able to sum the column values but ignored null values. To learn more about List functions I recommend this article with 200+ examples.

3. Conclusion

As shown in this article, there are several ways to sum null values. You could replace null values by 0, use error checking or make use of the List.Sum formula. Use any way you are comfortable with, but beware that null values don’t equal 0 and have their own specific behavior.

If you are new to this blog, make sure to sign up to my email list. Instead of you having to come here, the latest tutorials and blog posts will come to you. And I would love to see any questions or responses in the comments below. See you in the next one!

Share on:
  1. Pingback: How Power Query is the Excel myth buster
    • Hi Donald,

      Good point! In this case you could also write:

      = ( [Unit Price] * [Quantity] ) + [Tax Amount]??0
      

      In that case, when the Tax Amount is null the formula treats it as a 0. The ?? like you mention is the Power Query Coalesce operator. You can read more about null coalescing in this article: https://gorilla.bi/power-query/coalesce/

      Thanks for adding that to the conversation!

      Reply
  2. Hi,

    do you know which way has the best performance (= fastest calculation)?
    I work with a very huge amount of data and therefore performance is critical to me…

    Reply
  3. Hello,
    thank you for your helpfull article.
    Can you tell me how do to the difference between 2 columns containing null values ?

    Reply
    • Hi Sebastien,

      That’s a good question. You can do that by using the List.Sum function, like in my example. Just make sure to put the column you want to subtract in negative:

      = List.Sum( { [Column1],  - [Column2] } )
      

      Cheers,
      Rick

      Reply

Leave a comment