When your query returns an error, sometimes it could make sense to return an empty table instead of a simple error message. And ideally, this table has the same columns and types than the table that would have been returned without the error. Check out Chris Webbs article about it here: Chris Webb’s BI Blog: … Read more
While Power Query provides a convenient method to fill values from selected columns up- and downwards, there is no native option to do this to the right or left. So this article provides a custom function to do so. Table.FillRight in Power Query https://gist.github.com/ImkeF/d2031e6f6f401f22703334166cf1cae7 Usage to fill values to the right (or left) in Power … Read more
Have you ever come across the case where you open your dataflow and find 2 additional steps at the end of your query? Why are Power Platform Dataflows adding steps automatically at the end? It will be a step that replaces errors on certain columns followed by a step that transforms these columns into text. … Read more
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