# Excel NORM.DIST function in Power Query and Power BI

If you’re looking for Excel functions in Power BI, you will recognize that much more of them have been transformed to native DAX functions than to native M functions in Power Query. One of the basic statistical functions that hasn’t made it to M is the NORM.DIST function.

### Excel NORM.DIST function in Power Query

The Excel NORM.DIST function returns values for the normal distribution. And depending on the values of its 4th parameter (“Cumulative”: false or true), it returns either the values along the line (probability density function) or the area below the line (cumulative distribution function (CDF)).

Normal distribution functions (Source; https://en.wikipedia.org/wiki/Normal_distribution)

The version for the NORM.DIST function in Power Query I’m sharing here uses a very simple approximation for the calculation of the integral for the CDF. So it trades calculation speed for accuracy. Please share your solution of integral calculations in M in the comments, it will be very interesting to see how the M-engine handles these different approaches.

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.

 let func = (X as number, Mean as number, Standard_dev as number, Cumulative as logical) as number => if Cumulative = false then 1 / (Standard_dev * (Number.Sqrt(2 * Number.PI))) * Number.Power(Number.E, – 0.5 * (Number.Power((X – Mean) / Standard_dev, 2))) else // Very simple approximation – Source: https://www.hrpub.org/download/20140305/MS7-13401470.pdf [ NormZ = Number.Abs(X – Mean) / Standard_dev, Calc = ( (1 / Number.Sqrt(2 * Number.PI)) * ( Number.Power(Number.E, – (Number.Power(NormZ, 2) / 2)) / ( 0.226 + 0.64 * NormZ + 0.33 * Number.Sqrt(Number.Power(NormZ, 2) + 3) ) ) ), Result = if X < Mean then Calc else 1 – Calc ][Result], documentation = [ Documentation.Name = " Xls.NORMDIST.pq ", Documentation.Description = " Returns the normal distribution for the specified mean and standard deviation. ", Documentation.LongDescription = " Returns the normal distribution for the specified mean and standard deviation. https://support.microsoft.com/en-us/office/normdist-function-126db625-c53e-4591-9a22-c9ff422d6d58 ", Documentation.Category = " Xls.Statistical ", Documentation.Source = " www.TheBIccountant.com ", Documentation.Version = " 1.0 ", Documentation.Author = " Imke Feldmann ", Documentation.Examples = {[Description = " ", Code = " ", Result = " "]} ] in Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw

Xls.NORMDIST.pq

hosted with ❤ by GitHub

### How to use

The syntax for the NORM.DIST function in Power Query is identical to the Excel syntax:

1. X stands for the value for which you want the distribution
2. Mean is the arithmetic mean of the distribution
3. Standard_dev holds the Standard Deviation of the distibution and
4. Cumulative as a true or false selection indicates if area below the normal distribution line up until the X will be returned (cumulative distribution function) or just the point along the line (probability density function)

### Use cases

One prominent use case for this function is the Black Scholes calculation of options prices that I will present in my next blogpost.

Enjoy and stay queryious ðŸ˜‰