Getting unexpected results when summing up null values in Power Query is something beginners bump into all the time. And the reason for this is because null values have very particular behavior. So I wanted to put together an article that talks about 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.
Table of contents
Characteristics of null values
To explain 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 it a value. While a zero is an actual value, a 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 is not a zero.
null = null = true null = 0 = false
And what’s good to know is that 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
How to SUM null values
With this in mind we come to the issue many people experience in Power Query. Let’s imagine the following picture.
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 calculate the sum of null values, treating them as zero.
Method 1: Replace null by 0
One way to get the desired result is to replace the null values by zero’s. In that way, one does not have to deal with null values anymore. To do that
Right click on the desired column -> click Replace values -> Replace null by 0.
Method 2: Catch null values through Error Checking
Another way is to do error checking before adding up the values. Using a conditional if-statement you can check whether a column contains a null value. If that’s the case then you can write a formula to exclude the column with the null value.
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.
Method 3: Use the function List.Sum
Last but not least, 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. 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.
As shown in this article, there are several ways to handle 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 behaviour.
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!