Excel NORM.INV function in Power Query and Power BI

The Excel NORM.INV function hasn’t made it into the standard library of M functions for Power Query yet. So here I’m sharing a custom function that replicates it.

Excel NORM.INV function in Power Query

The Excel NORM.INV function returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. So unlike the NORM.DIST function, that returns the probability of a threshold value to occur under the normal distribution (in CDF mode), this function returns the threshold value that matches a given probability.

Again, the parameters of this function fully match the Excel function syntax. Unfortunately I still don’t know how Excel does the exact calculation for it, so I’m using another approximation that I’ve found on the web:


let func =
(probability as number, mean as number, standard_dev as number) as number => 
    let
        // Source for NormalCDFInverse: https://www.johndcook.com/blog/normal_cdf_inverse/
        //StdDev = 1.5,
        //Mean = 2,
        p = probability,
        
RationalApproximation = (t as number) => 
            [c = {2.515517, 0.802853, 0.010328}, d = {1.432788, 0.189269, 0.001308}, return
                = t – ((c{2} * t + c{1}) * t + c{0}) / 
(((d{2} * t + d{1}) * t + d{0}) * t + 1)][return],
        NormalCDFInverse = if (p < 0.5) 
then – RationalApproximation(Number.Sqrt(- 2 * Number.Log(p))) 
else RationalApproximation(Number.Sqrt(- 2 * Number.Log(1 – p))),
        DenormCDFInverse = NormalCDFInverse * standard_dev + mean
    in
        DenormCDFInverse ,
documentation = [
Documentation.Name = " Xls.NORMINV.pq ",
Documentation.Description = " Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. ",
Documentation.LongDescription = " Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. https://support.microsoft.com/en-us/office/norm-inv-function-54b30935-fee7-493c-bedb-2278a9db7e13 ",
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.NORMINV.pq

hosted with ❤ by GitHub

 

How to use the NORM.INV function

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

  1. probability under the normal distribution
  2. mean is the arithmetic mean of the distribution
  3. standard_dev holds the Standard Deviation of the distibution

Alternatives

If you want to use this function to generate random numbers along the normal distribution curve, I also recommend to check out Sandeep Pavars version here.

Sample File

Please check out the sample file: ExcelNORMINV_Sample.xlsx

Enjoy and stay queryious 😉

Comment (1) Write a comment

  1. Pingback: Implementing NORM.INV in Power Query – Curated SQL

Leave a Reply