List.Sum - List Function

List.Sum is a Power Query M function that returns the sum of the non-null values in a list. The function returns the sum of the non-null values or null if there are no non-null values in the list.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.Sum(
   list as list,
   optional precision as nullable number,
) as any

Argument	Attribute	Description
list		This is the list of values to sum.
precision	optional	The Precision.Type specifies the accuracy level for calculations. When omitting this argument, Power Query uses Precision.Double by default, which, while efficient, may cause rounding errors with very small fractions. For greater accuracy Precision.Decimal offers a more precise number representation, ideal for tasks requiring high precision.

Description

The List.Sum function takes a list of values as input and adds these together. When the list does not contain any values other than null, it returns null. You can optionally provide a Precision.Type to specify the level of detail for your calculations.

Examples

Let’s look at some examples of the List.Sum function.

Adding Number Values

The List.Sum function in its most basic setup, grabs a list of numbers and adds them up. For instance, when you provide it with the list { 1500, 500, 100 } you can use it as follows:

List.Sum( { 1500, 500, 100 } ) // Output: 2100

Here the expression performs a simple addition of the provided numbers in the list.

Handling Null Values

Now you may run into situation where you want to apply the List.Sum function on a column in your table. It is possible that that column contains both numbers and null values. So what happens when it runs into null values?

For instance, take the below example that is adjusted from the previous one:

// Output: 2100
List.Sum( { null, 1500, 500, null, null, 100 } )

In this scenario, the function ignores all null values. It removes all non-null values before performing any operation on them. It then adds up the remaining values and returns 2100 once again, just like before.

Specifying Precision

Now you may run into situation with numbers that have a lot of decimals. For instance:

// Output: 0,44444444444444442
List.Sum( { 0.33333333333333333333, 0.11111111111111111111 } )

By default, the List.Sum function uses a Precision.Type called Precision.Double. Knowing this, the following expression is identical to one presented above:

// Output: 0,44444444444444442
List.Sum( { 0.33333333333333333333, 0.11111111111111111111 }, Precision.Double )

Interestingly, we would have expected the last decimal value of the addition to be 4. How come we it returns us a different value? The reason is, the precision level is too low. the double floating point precision can not hold that level of precision.

If we would change the precision enumeration to Precision.Decimal, we would have the desired result:

// Output: 0,44444444444444444444
List.Sum( { 0.33333333333333333333, 0.11111111111111111111 }, Precision.Decimal )

So whenever you need a high level of detail, make sure you specify the Precision.Decimal enumeration.

Learn more about List.Sum in the following articles:

Power Query Precision: Avoid Rounding Errors
Learn how to handle precision differences in Power Query. With these tips you can return accurate numbers and prevent rounding errors. » Read more
Create Running Totals in Power Query M (Ultimate Guide)
Learn how to easily create a running total in Power Query. Tried other approaches that are slow? These tricks will get you your cumulative values FAST! » Read more
4 Ways to SUM Null Values in Power Query M
When you sum null values, you may get unexpected results. Null value represents the absence of a value and has special characteristics. » Read more
Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more

Other functions related to List.Sum are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-sum