TRIMMEAN is a statistical function in Excel that calculates the “mean taken by excluding a percentage of data points from the top and bottom tails of a data set”. So you can use it if you want to exclude potential outliers from your data. Daniil Maslyuk has a nice approach for it in DAX, but … Read more
If you are looking for the Excel equivalent of the WEEKNUM function for Power Query this article is for you. As this is also a function that has many regional options, I was lucky to find an algorithm that I could use for its main part here: M functions to convert between ISO 8601 Week … Read more
While there is a native YEARFRAC-function in DAX that you can use to calculate year fractions, sometimes you might just need it in Power Query. The function I’m presenting here has the same function arguments than its Excel-equivalent. Its 5 different modes require some advanced calculations. Fortunately I found a great resource on how to … Read more
If you want to transform your Excel calculations over to Power Query you might appreciate functions there that have the same syntax like in Excel itself. Today I’m going to share the Power Query function for the Excel WORKDAY function. It has exactly the same function parameters than its Excel equivalent. (If you came here … Read more
In this article I’m going to present a method about transforming multiple columns at once in a fast way. This method also allows you to reference columns that exist in your table already. As I have described in a previous article, this cannot be done using the native Table.TransformColumns function that will be applied if … Read more
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 … Read more