Value.Subtract - Value Function

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

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

Syntax

Value.Subtract(
   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.Subtract function subtracts one value from another. While subtraction using the standard - operator is straightforward, Value.Subtract allows you to specify the level of precision through Precision.Double (default) or Precision.Decimal. This can help you avoid rounding errors in calculations involving fractional numbers.

Examples

Let’s explore how Value.Subtract works, its default behavior, and how to adjust its precision when needed.

Basic Usage

The Value.Subtract function takes two values as input and returns the result of subtracting the second value from the first. Here’s a simple example:

Value.Subtract( 10, 5 ) // Returns 5

This operation is equivalent to writing:

10 - 5 // Returns 5

By default, Value.Subtract uses Precision.Double, the default representation for fractional numbers in the M language. However, this can sometimes lead to minor rounding errors due to how numbers are stored internally.

Precision and Rounding Errors

When using Precision.Double, binary representations of fractional values may introduce small inaccuracies. These rounding errors can result in unexpected outcomes in seemingly simple calculations.

For example:

Value.Subtract( 1, 0.7 )                   // Returns 0.30000000000000004
Value.Subtract( 1, 0.7, Precision.Double ) // Returns 0.30000000000000004

In this case, the result of subtracting 0.7 from 1 is not exactly 0.3 but includes a small error (0.30000000000000004). This occurs because Precision.Double approximates fractional values. While this difference is negligible in most scenarios, it can lead to errors for tasks requiring a higher level of precision.

Improving Accuracy with Precision.Decimal

If you need more precise results, you can use Precision.Decimal instead. This option stores numbers using a fixed decimal system, avoiding the rounding issues associated with binary representation.

Here’s how it works:

Value.Subtract( 1, 0.7, Precision.Decimal ) // Returns 0.3

By specifying Precision.Decimal, the calculation produces the exact result of 0.3, eliminating the rounding error.

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

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