Value.Divide - Value Function

Value.Divide is a Power Query M function that calculates the result of dividing value1 by value2, with an optional precision parameter. The function returns the division result, using the specified precision if provided.

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

Syntax

Value.Divide(
   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.Divide function divides one value by another, also offering control over precision. While division can be performed directly using the / operator, Value.Divide allows you to specify either Precision.Double (default) or Precision.Decimal, providing flexibility for handling fractional results with greater accuracy.

Examples

Let’s look at how Value.Divide works, how it handles precision by default, and how you can use it to avoid rounding errors in your calculations.

Basic Usage

The Value.Divide function divides one number (the numerator) by another (the denominator) and returns the result. Here’s a simple example:

Value.Divide( 10, 2 ) // Returns 5

This produces the same result as the standard division operator:

10 / 2 // Returns 5

By default, Value.Divide uses Precision.Double, a common system that represents numbers in binary. While efficient for most calculations, it can sometimes produce small rounding errors when working with fractions.

Precision and Rounding Errors

When using Precision.Double, binary representation can struggle to represent some decimal fractions exactly, leading to minor inaccuracies. For example:

// Both Return: 0.09999999999999999
Value.Divide( 0.3, 3 )                   
Value.Divide( 0.3, 3, Precision.Double )

Here, dividing 0.3 by 3 with Precision.Double results in a small error instead of the expected 0.1. This occurs because Precision.Double approximates decimal fractions in binary format. For everyday use, this is usually fine, but for high-precision calculations, it might not be acceptable.

Improving Accuracy with Precision.Decimal

If you need more precise results, you can use Precision.Decimal. This option avoids binary rounding errors by representing numbers in a fixed decimal format. Here’s an example:

// Returns: 0.1
Value.Divide( 0.3, 3, Precision.Decimal )

With Precision.Decimal, the result is precisely 0.1, as expected. This makes it the preferred option for calculations where accuracy is important.

Handling Division by Zero

If you attempt to divide by zero using Value.Divide, it doesn’t throw an error. Instead, it returns infinity for positive numerators or negative infinity for negative numerators:

Value.Divide( 10, 0 )  // Returns infinity (∞)
Value.Divide( -10, 0 ) // Returns negative infinity (-∞)

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

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