Downloads
Power Query MNumberIntermediate

Number.Sqrt

Returns the square root of a number.

Syntax
Number.Sqrt(number as nullable number) as nullable number

Returns: The square root, or null when the input is null. An error for a negative input.

What it does

Number.Sqrt returns the square root of a number. In finance it appears in standard deviation, volatility and some distance measures. A variance report that shows the spread of monthly results uses a square root to turn the variance into a standard deviation, which is in the same units as the amounts.

Syntax and arguments

Example

A controller wants the typical size of monthly swings in a cost line. The variance of the monthly amounts is calculated, and the square root gives the standard deviation.

let
    Monthly = {100, 110, 90, 120, 80},
    Average = List.Average(Monthly),
    Squares = List.Transform(Monthly, each Number.Power(_ - Average, 2)),
    Variance = List.Sum(Squares) / (List.Count(Monthly) - 1),
    StdDev = Number.Sqrt(Variance)
in
    StdDev

The average is 100, the sample variance is 250, and the standard deviation is about 15.8. The result is the typical distance of a month from the average, in dollars.

Example: a second, different use

Take the square root of a measure to scale a risk score, such as the standard deviation of a customer's payment days.

Things to watch

In business terms

Volatility, standard deviation and the spread of monthly results all start with a square root. A finance team that shows the spread with the average gives the reader a fair sense of how stable a line really is.

Related functions

All Power Query M functions · View as Markdown · M basics