List.Sum

Updated on

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
ArgumentAttributeDescription
listThis is the list of values to sum.
precisionoptionalThe 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:

Other functions related to List.Sum are:

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