List.Sum in Power Query: 10 simple examples

In this post, we look at the List.Sum Power Query M function. It is an addition function that returns the sum of a list.

Along with detailed notes, there are 10 examples to show how to use List.Sum in different contexts.

Table of Contents:

Purpose
Return Value
Syntax
Parameters
Usage Notes
Examples
Related Tutorials
Related Functions

Purpose

Returns the sum of non-null values from a list; or returns null where there are no non-null values.

Return Value

A number representing the sum of the non-null items from a list. Or null if the list contains no non-null items.

Syntax

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

Parameters

list – A Power Query list. This list can be:
- Individual comma-separated items contained within curly brackets, e.g. {1,2,3}
- Existing query/step with a list type as an output.
- Function which returns a list, e.g., List.Combine
precision – an optional argument specifiying the level of accuracy. The values can be:
- Precision.Double or 0 – applies standard precision based on the IEEE 754 standard. This is the default if no argument is provided.
- Precision.Decimal or 1 – exceptionally high level of precision but requires more memory for additional accuracy.

Usage Notes

The List.Sum function is in the List function category; it returns a single value from a list supplied.

List.Sum is available in two locations in the standard user interface:

Transform [1] > Statistics [2] > Sum [3]

List.Sum included in the standard interface

List Totals [1]: Statistics [2] > Sum [3]

Examples

Example #1: Sum items in a list

The parameter can be a standard M code list. This is a comma-separated list held within curly brackets.

The function below sums 4 numbers.

= List.Sum({1, 2, 3, 4})

Returns: 10

Example #2: Sum from a separate list query

Where another query or step has a list output, that can be used as the list parameter.

The following query, called Example List, has a list output.

= {1, 2, 3, 4}

The list query above can be used inside the List.Sum function.

= List.Sum(#"Example List")

Returns: 10

Example #3: Sum from a single Table column

A single table column is a list. This can be used as the list parameter inside the List.Sum function.

The table above is named Example Table.

The code below sums the items in the Value column.

= List.Sum(#"Example Table"[Value])

Returns: 100

Example #4: Sum from multiple lists

Where multiple lists are required, List.Combine can join two or more lists prior to using List.Sum.

The example below returns the sum of the Example List query from Example 2 and the numbers 5 and 6.

= List.Sum(List.Combine({#"Example List",{5, 6}}))

List.Combine evaluates to: {1,2,3,4,5,6}.

The list returned by List.Combine is calculated inside List.Sum.

Returns: 21

Example #5: Sum vs. addition with null values

Null values in Power Query are a special type. Null is nothing; it is the non-existence of something. 0 is the existence of something, but there is none of it. As a result, 0 and null are not the same value.

Using the Example Table from Example 3, if a new column were added to sum the Value, Discount, and Tax values, row 2 would display null.

= Table.AddColumn(#"Changed Type", "Addition", 
  each [Value]+[Discount]+[Tax], Int64.Type)

In Row 2:

Value = 20
Discount = null
Tax = 2

Any standard calculation involving null will always return null.

Returns: Table with null in row 2

However, List.Sum only considers non-null values.

If a column were added using the same columns inside List.Sum, Row 2 would calculate correctly.

= Table.AddColumn(#"Added Custom", "List.String", 
  each List.Sum({[Value], [Discount], [Tax]}), Int64.Type)

Returns: Table with 22 in Row 2

Example #6: Sum different data types

Sum can only be performed on numeric types. In Excel, we may think of dates as numbers with custom formatting applied. However, in Power Query, dates are a type that cannot be used within List.Sum.

= List.Sum({1, #date(2030,1,1)})

Returns: Expression.Error: We cannot convert the value #date(2030, 1, 1) to type Number.

DateTime, Duration, and Time have similar attributes to date data types. Any non-numeric data type must be converted to a number before usage inside List.Sum.

In the formula below Number.From converts the date to a number.

= List.Sum({1, Number.From(#date(2030,1,1))})

Returns: 47485

Example #7: Sum different number types

Within Power Query, there are different number formats:

Literal: 1
Decimal: 1.2
Exponential: 1e2 (= 100)
Hexidecimal: 0x0A (= 10)

These formats are all considered numbers and can be included within List.Sum.

= List.Sum({1, 1.2, 1e2, 0x0A})

Returns: 112.2

For further resources on numbers in Power Query, check out this post from Ben Gribaudo: Power Query M Primer (Part 7): Types—Numbers.

Example #8: Sum with increased levels of precision

In computing, there is an issue with the conversion between decimal and binary numbers. As a result, some calculations return small differences.

In Power Query, surprisingly, 0.1 + 0.2 does not equal 0.3, but 0.30000000000000004

= List.Sum({0.1, 0.2})

Returns: 0.30000000000000004

The formula above applies the default precision setting, which is Precision.Double (or we could enter 0).

The formula below applies the Precision.Double parameter; therefore, it returns the same result.

= List.Sum({0.1, 0.2},Precision.Double)

Returns: 0.30000000000000004

Just as Precision.Double has tiny differences on small numbers, it also has tiny differences on massive numbers.

As a result, 10000000000000000 + 1 does not equal 10000000000000001.

= List.Sum({10000000000000000, 1})

Returns: 10000000000000000

Within List.Sum, there is a higher level of precision available by applying Precision.Decimal into the Precision parameter.

The formulas below provide the correct results using Precision.Decimal (or we could use 1).

= List.Sum({0.1, 0.2}, Precision.Decimal)

Returns: 0.3

= List.Sum({10000000000000000, 1}, Precision.Decimal)

Returns: 10000000000000001

Example #9: Sum for a running total

List.Sum is the basis for other common calculations, such as a running total.

Using the Example Table as the start point, some columns have been removed, and an index column added.

The following formula is a custom function to include a running total column.

= List.Sum(List.FirstN(#"Added Index"[Value],[Index]))

Custom Column using List.Sum in Running Total

Returns: A column with a running total {10,30,60,100}

For more examples of List.Sum to create a running total, check out the following:

Example #10: Sum an empty list

With List.Sum, zero and empty are not the same.

The formula below evaluates to zero.

= List.Sum({1, -1})

Returns: 0

While the formula below evaluates to null because the list is empty

= List.Sum({})

Returns: null

The formula below proves Null and 0 are not the same.

= List.Sum({}) = List.Sum({1, -1})

Returns: FALSE

Related Functions

List.Average