Is there a List function in Power Query to get the STABW.N and not the STABW.S

Question

I use a combination of the List.StandardDeviation function in Excel Power Query to get the Standard Deviation of each item.

The List.StandardDeviation seems to be the same function as the "STABW.S" on a normal excel sheet. Is there another function that is equal to the "STABW.N" of an normal excel sheet?

Answer 1

Hi @Bauer, Harald - Xylem,

I believe you are asking if there is a function in Power Query that returns the population standard deviation, equivalent to Excel’s STDEVP function. In Power Query, you can use the List.StandardDeviation function to calculate the sample standard deviation for a list of values. However, this function does not have an option to calculate the population standard deviation (STDEVP in Excel). The List.StandardDeviation function implements the sample standard deviation, which has a slightly different formula than the population standard deviation.

To calculate the population standard deviation in Power Query, you can use the Number.Power, List.Average, and List.Count functions to calculate the variance of the population and then take the square root of this result. The formula should look like this:

= Number.Power(List.Average(List.Transform(values, each _ - List.Average(values))),2) / (List.Count(values) - 1) = Number.Sqrt(Number.Power(List.Average(List.Transform(values, each _ - List.Average(values))), 2) / (List.Count(values) - 1))

Please note, this formula uses (List.Count(values)-1) as the denominator, unlike the two-sample formula that List.StandardDeviation uses, which divides by (List.Count(values))-2).

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.