Value.Multiply - Value Function

Value.Multiply is a Power Query M function that calculates the product of multiplying value1 by value2, with an optional precision parameter. The function returns the product of the two input values, using the specified precision if provided.

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

Syntax

Value.Multiply(
   value1 as any,
   value2 as any,
   optional precision as nullable number,
) as any

Argument	Attribute	Description
value1
value2
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 Value.Multiply function multiplies two values and allows you to control the precision used in the calculation. While you can simply use the * operator for multiplication, Value.Multiply allows you to specify either Precision.Double (default) or Precision.Decimal. This can help you avoid rounding errors in calculations involving fractional numbers.

Examples

Let’s see Value.Multiply works, the impact of precision settings, and how you can use it for your calculations.

Basic Usage

The Value.Multiply function takes two values as inputs and returns their product. Here’s a simple example:

Value.Multiply( 6, 2 ) // Returns 12

This operation is similar to writing:

6 * 2 // Returns 12

By default, Value.Multiply uses Precision.Double, which represents numbers in binary. This is sufficient for most scenarios but can give rounding errors when dealing with fractional values.

Precision and Rounding Errors

When using Precision.Double, rounding errors can occur because binary representation struggles to precisely store some decimal fractions. Let’s look at this behavior with an example:

Value.Multiply( 6, 0.1 )                   // Returns 0.60000000000000009
Value.Multiply( 6, 0.1, Precision.Double ) // Returns 0.60000000000000009

Here, multiplying 6 by 0.1 using Precision.Double produces a result with a small error (0.6000000000000001) instead of the expected 0.6. This happens because Precision.Double approximates decimal fractions in binary.

While this level of accuracy is sufficient for most cases, it can be problematic in calculations requiring exact results.

Improving Accuracy with Precision.Decimal

To avoid rounding errors, you can use Precision.Decimal. This precision type stores numbers in a fixed decimal format, which returns more accurate results. Here’s how Value.Multiply works with Precision.Decimal:

Value.Multiply( 6, 0.1, Precision.Decimal ) // Returns 0.6

By using Precision.Decimal, the result is exactly 0.6. This eliminates any rounding errors. This makes it a better choice for calculations that require precision.

Learn more about Value.Multiply 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

Other functions related to Value.Multiply are:

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