Number.Epsilon is a Constant Value in the Power Query M language that represents the smallest positive number a floating-point number can hold. This constant is useful in performing precision-sensitive calculations where you need to evaluate whether a floating-point number is practically zero, or in dealing with very small number calculations that would otherwise get rounded down to zero.
To retrieve the constant value representing the smallest positive number that can be represented in Power Query, you can directly call the Number.Epsilon function without using any parentheses. This simple and concise approach allows you to obtain the precise value of the constant:
Number.Epsilon // Returns 4.94065645841247E-324
To get a better understanding the Epsilon number is used, here are two examples.
Precision-Sensitive Calculations: Number.Epsilon is valuable when performing calculations that require high precision, especially when comparing floating-point numbers. You can use Number.Epsilon to determine if the difference between two numbers is within an acceptable tolerance level:
if Number.Abs( Number1 - Number2 ) < Number.Epsilon then "Numbers are considered equal" else "Numbers are not equal"
Approximating Zero: Number.Epsilon helps determine if a floating-point number is practically zero by comparing it with a tolerance level. For example, you can check if a number is close to zero within a certain threshold:
if Number.Abs( NumberX ) < Number.Epsilon then "Number is practically zero" else "Number is not zero"
Division by Zero Handling: Number.Epsilon can be used to prevent division by very small numbers that might result in a potential error. You can add Number.Epsilon to the denominator to ensure a minimum divisor value:
Numerator / ( Denominator + Number.Epsilon )
Other related constants are: