Value.Add - Value Function

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

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

Syntax

Value.Add(
   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.Add function adds two values together, with the flexibility of specifying precision. Unlike regular addition (+), Value.Add allows you to choose between Precision.Double (default) or Precision.Decimal, giving you greater control over how fractional values are handled.

Examples

Let’s break down how Value.Add works, its default behavior, and how you can adjust its precision when necessary.

Basic Usage

The Value.Add function takes two values as input and returns their sum. Here’s a simple example:

Value.Add( 5, 10 ) // Returns 15

This operation is equivalent to writing:

5 + 10 // Returns 15

By default, Value.Add uses Precision.Double, a common system for representing fractional values in computing. While efficient, this system can sometimes lead to minor rounding errors due to how numbers are stored internally.

Precision and Rounding Errors

When using Precision.Double, numbers are represented in binary format, which sometimes struggles to precisely represent certain fractional values, like 0.1 or 0.2. These small discrepancies can lead to unexpected results in seemingly simple calculations.

Here’s an example:

Value.Add( 0.2, 0.1 )                   // Returns 0.30000000000000004
Value.Add( 0.2, 0.1, Precision.Double ) // Returns 0.30000000000000004

Notice that instead of 0.3, the result includes an unexpected small error (0.30000000000000004). This happens because Precision.Double approximates the values rather than representing them exactly. While the difference is usually negligible, it can be problematic in scenarios where you need high precision.

Improving Accuracy with Precision.Decimal

If you require more exact calculations, you can use Precision.Decimal instead of the default. This precision type stores numbers with a fixed decimal system, avoiding the rounding issues associated with Precision.Double.

Here’s the same calculation using Precision.Decimal:

Value.Add( 0.2, 0.1, Precision.Decimal ) // Returns 0.3

With Precision.Decimal, the result is precise and as expected: 0.3.

Learn more about Value.Add 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.Add are:

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