4 Ways to SUM Null Values in Power Query M

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

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

Table of contents

• 1. Problem with Summing Null Values
• 2. Understanding
• 3. Methods to Sum Null Values
  • 3.1. Replace Null by 0
  • 3.2. Check Null with If Statement
  • 3.3. Use the Function List.Sum
  • 3.4. Use the Coalesce Operator
• 4. Conclusion

1. Problem with Summing Null Values

Have you ever come across a null value in Power Query and tried to add it to a number? If you have, you might have noticed that the result is always null. This behaviour can be confusing, and it’s not what most people expect to happen. In fact, many people assume that null values should be treated as zeroes when added to a number.

Let’s take a look at a common scenario: you have a dataset with three columns: Product Name, Price Excl VAT, and Tax Amount (if available) for each of the products. As a user, you want to calculate the Price, including VAT. It should be as simple as adding the Tax Amount to the Price Excl VAT, right?

Unfortunately, things get a bit more complicated when some of the rows have null values in the Tax Amount column. In fact, when you try to add a null value to a number, you end up with null as the result. This can be frustrating and confusing, especially if you’re new to Power Query.

Before looking into methods to deal with it, we’ll dive deeper into null values and help you gain a better understanding of them before we explore methods to deal with them.

2. Understanding Null Values in Power Query

First and foremost, null values indicate an empty cell or the absence of any data. And it’s important to note that null is not actually considered a value. Unlike zero, which is a legitimate value, null is simply the absence of a value. And these null values have some funky characteristics.

For example, a null value in a formula is not the same as zero, and it’s not the same as a blank value either. In fact, when comparing null values to other values, the results can be unexpected.

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

Another thing to keep in mind 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

This can be particularly important to understand when you’re writing code, as you may want it to perform a specific action if a null value is encountered.

Knowing this, you have a better understanding of what null values are and how they behave in the M language. So, what methods can you use to SUM null values?

3. Methods to Sum Null Values

In this chapter, we will explore 4 different methods to sum null values in Power Query. These methods will help you to sum values accurately, even when null values are present.

3.1. Replace Null by 0

The first method to sum null values is to simply replace null values with 0. This is a simple and straightforward method that doesn’t require complex formulas. By replacing null values with 0, you eliminate the null values and can then proceed to sum the values without any issues.

To replace null values with 0 in Power Query, follow these simple steps:

1. Right-click on the column that contains null values.
2. Select “Replace Values” from the drop-down menu.
3. In the “Replace Values” dialog box:
   • enter “null” (without quotes) in the “Value to Find” field.
   • enter “0” (without quotes) in the “Replace With” field.
   • click “OK” to replace all null values in the column with 0.

3.2. Check Null with If Statement

Another way to deal with null values is by using a conditional if statement to check for null values before summing them. In Power Query, you can use the if-then-else expression to create a conditional statement that checks for null values.

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

if [Tax Amount] = null 
   then [Price Excl VAT] 
   else [Price Excl VAT] + [Tax Amount]

In this example, we’re checking if the Tax Amount column contains a null value. If it does, we return the Price Excl VAT column value. If it doesn’t, we add the Tax Amount value to the Price Excl VAT value.

This method works well when you have a few columns with null values but can become tedious when you have many columns. You would have to repeat the if-then-else expression for each column, which can be time-consuming and error-prone.

In the next section, we’ll explore a more efficient solution that can handle null values in multiple columns at once.

3.3. Use the Function List.Sum

When summing null values, using the List.Sum function in Power Query can be a robust solution. This function returns the sum of the non-null values in the provided list. If there are no non-null values in the list, it returns null.

To use this List.Sum method, you need to provide your values comma-separated within curly brackets. A single value in the list can also be a calculation.

Here is an example of how to use the List.Sum function in Power Query:

= List.Sum( { 
              [Price Excl VAT], 
              [Tax Amount] 
            } )

When new columns are added, it’s also very easy to adjust the formula.

This method allows you to sum the column values but ignore null values. If you want to learn more about List functions, we recommend checking out this article with over 200 examples.

3.4. Use the Coalesce Operator

Another method to sum null values in Power Query is by using the Coalesce operator “??” which returns the first non-null value in a list of arguments. If you’re new to the Power Query coalesce operator, this article has you covered.

To use the Coalesce operator in Power Query, simply list the column(s) you want to sum as arguments in the Coalesce function. Then, behind this column, add the Coalesce Operator “??” followed by the alternative you want to return when it encounters a null value. Power Query will then return the first non-null value.

For example, suppose you have the above table with columns for Price Excl VAT and Tax Amount. To sum these two columns and return zero if the [Tax Amount] column has a null value, you can use the Coalesce operator as follows:

= [Price Excl VAT] + ( [Tax Amount]??0 )

This formula checks if the value in the column [Tax Amount] is null. If it is null, then it returns zero, otherwise, it returns the value in the [Tax Amount] column. The resulting values are then added together with the [Price Excl VAT] column to get the sum of the two columns, with null values treated as zeros.

This method is particularly useful when you have a large number of columns to sum, as it allows you to handle null values in a concise and flexible manner. Just be careful with the brackets. In some scenarios, the result is wrong if you don’t make use of the brackets for your coalesce operator.

4. Conclusion

In conclusion, null values in Power Query can be a tricky problem to deal with, but fortunately, there are several methods to solve it.

You could replace null values with 0, use error checking, and make use of the List.Sum formula or make use of the coalesce operator. Use any way you are comfortable with, but beware that null values don’t equal 0 and have their own specific behaviour.

Each of these methods has its own strengths and weaknesses, and the best one for you will depend on your specific scenario. Whether you’re dealing with a few columns or many, there is a solution that will work for you. With these methods in hand, you’ll be well-equipped to tackle null values in Power Query and get the results you need.

Happy Querying!