List.Product

Updated on

List.Product is a Power Query M function that calculates the product of the non-null numbers in a list. The function returns the product 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.Product(
   numbersList as list,
   optional precision as nullable number,
) as nullable number
ArgumentAttributeDescription
numbersListThis is the list of numbers to multiply.
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.Product function takes a list of values as input. It then removes all null values from the list and multiplies the remaining values. The function returns a null value if it receives no values other than null.

Examples

Let’s look at some examples.

Multiplying Numbers

In this first example, we try to find the product of a list of numbers:

// Output: 6
List.Product( { 1, 2, 3 } )

In this sequence, all values are non-null. The function therefore performs the following operation: 1 * 2 * 3 = 6.

Ignoring Null Values

Now suppose your list would contain a mix of null values and other values. Here’s an example:

// Output: 10
List.Product( { null, null, 2, null, 5 } )

In this scenario you can find three null values. How does that List.Product function handle this? The function has some error handling built in. What it does is, before multiplying any value, the function removes all null values. In this scenario it ends up with the values 2 and 5. It then multiplies 2 * 5 and returns 10 as a result.

Specifying Precision

Now suppose we have very detailed numbers as in the following example:

// Output: 0,037037037036629625
List.Product( { 0.33333333333, 0.111111111111 } )

The outcome of this operation is 0,037037037036629625. This expression is identical to the following one that specifies a Precision.Type:

List.Product( { 0.33333333333, 0.111111111111 }, Precision.Double )

You may run into situation where you need a higher level of precision. In that scenario, you could change the Precision.Type enumeration to Precision.Decimal. The outcome will be a number that allows for a higher level of detail:

// Outcome: 0,03703703703662962962963
List.Product( { 0.33333333333, 0.111111111111 }, Precision.Decimal )

Learn more about List.Product in the following articles:

Other functions related to List.Product are:

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