List.StandardDeviation

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.StandardDeviation

What is Standard Deviation?

Before we dive into the M code behind the List.StandardDeviation function, it is important to understand what standard deviation is. Standard deviation is a measure of the variability or dispersion of a set of data values. It measures how spread out the values are from the mean or average value. The higher the standard deviation, the more spread out the values are, and the lower the standard deviation, the more tightly clustered the values are.

The List.StandardDeviation Function

The List.StandardDeviation function is used to calculate the standard deviation of a list of numbers. The syntax of the function is as follows:

List.StandardDeviation(list as list, optional options as nullable record) as nullable number

The List.StandardDeviation function takes two arguments. The first argument is the list of numbers for which we want to calculate the standard deviation. The second argument is an optional record that contains options for calculating the standard deviation. The function returns the standard deviation of the list of numbers as a nullable number.

The M Code Behind the List.StandardDeviation Function

The M code behind the List.StandardDeviation function is a bit complex, but it can be broken down into several steps. Let's take a look at the M code behind the function:

let

Source = List.Average(list),

SumOfSquares = List.Sum(List.Transform(list, each _ _)),

Count = List.Count(list),

StandardDeviation = if Count = 0 then null else if Count = 1 then 0 else

let

Variance = (SumOfSquares – Count Source Source) / (Count – 1)

in

if Variance < 0 then null else Number.Sqrt(Variance)

in

StandardDeviation

The M code starts by calculating the average of the list of numbers using the List.Average function. Next, it calculates the sum of squares of the numbers in the list using the List.Sum and List.Transform functions. The List.Transform function squares each number in the list using the expression "each _ _".

After calculating the average and sum of squares, the M code calculates the count of the numbers in the list using the List.Count function. Finally, it calculates the standard deviation using the formula:

StandardDeviation = sqrt((SumOfSquares – Count Source Source) / (Count – 1))

The M code checks for a few edge cases as well. If the count of the numbers in the list is zero, the function returns null. If the count is one, the function returns 0. If the variance calculated using the formula is negative, the function returns null.

The List.StandardDeviation function is a powerful tool for calculating the standard deviation of a list of numbers in Power Query. The M code behind the function is complex, but it can be broken down into several steps. Understanding the M code behind the function can help you better understand how the function works and how to use it effectively in your data analysis and transformation tasks.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)