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:
https://gist.github.com/ImkeF/973601216d9f5a3967215b71d7c5f55f
How to use the NORM.INV function
The syntax for the NORM.INV function in Power Query is identical to the Excel syntax:
- probability under the normal distribution
- mean is the arithmetic mean of the distribution
- 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 😉
1 thought on “Excel NORM.INV function in Power Query and Power BI”