Value.Compare - Value Function

Value.Compare is a Power Query M function that compares two input values and returns -1, 0, or 1 based on whether the first value is less than, equal to, or greater than the second one. The function returns an integer indicating the comparison result between the two input values.

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

Syntax

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

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.Compare function compares two values in Power Query M and determines their relationship. It returns an integer to indicate whether the first value is less than, equal to, or greater than the second value. By default, Value.Compare uses Precision.Double to evaluate numeric values, but you can also specify Precision.Decimal for more precise comparisons.

Examples

Let’s explore how Value.Compare works and look at the difference between the Double and Decimal precision modes.

Basic Usage

The Value.Compare function returns:

-1 if the first value is less than the second.
0 if the two values are equal.
1 if the first value is greater than the second.

Here’s a basic example:

Value.Compare( 5, 10 ) // Returns -1
Value.Compare( 10, 10 ) // Returns 0
Value.Compare( 15, 10 ) // Returns 1

The function determines the relationship between the two values, returning a result that reflects their order.

Precision and Comparison

By default, Value.Compare uses Precision.Double, which represents numbers in binary format. This can cause two values that are technically different to appear identical due to the limitations of binary floating-point representation.

Consider the following example:

Value.Compare( 0.3, 0.300000000000000001 )                   // Returns 0
Value.Compare( 0.3, 0.300000000000000001, Precision.Double ) // Returns 0

In this case, even though the second value is slightly larger than the first, Precision.Double considers them identical due to the way it approximates fractional numbers. The function therefore returns 0, indicating equality.

Using Precision.Decimal for Exact Comparisons

If you need more accurate comparisons, you can use Precision.Decimal. This precision mode evaluates the values as fixed decimal numbers, avoiding the rounding errors common in binary representation.

Here’s the same comparison with Precision.Decimal:

Value.Compare( 0.3, 0.300000000000000001, Precision.Decimal ) // Returns -1

With Precision.Decimal, the function accurately identifies that 0.3 is less than 0.300000000000000001, and it returns -1 as expected.

Learn more about Value.Compare 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
Mastering List.Sort in Power Query M: Comprehensive Guide
Learn to master the List.Sort function in Power Query. Create custom sort orders using the comparison criteria for advanced data sorting, beyond the basics. » Read more

Other functions related to Value.Compare are:

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