While Excel and DAX have native functions to calculate the payment amount for a loan, Power Query lacks it still. As this is a very useful function, it’s time to create our own Payment function (PMT) for Power Query: Excel’s payment function (PMT) for Power Query Excel’s PMT function returns the payment for a loan … Read more
While Excel and DAX have native functions to calculate the future value of a payment series, Power Query lacks it still. So it’s time to create our own function for the Future Value (FV) for Power Query: Excel’s function for the Present Value (FV) for Power Query Excel’s FV function returns the future value of … Read more
Finally 2023 is here, the year we expect Power Query function libraries to ship. This will make it so much easier to re-use ready made function than M-extensions. So let’s start collecting some fodder for it, by creating a function to calculate the Present Value (PV) for Power Query: Excel’s Present Value (PV) function for … Read more
I like Power BI scorecards a lot, as they give a really nice interface and look to your and your teams goals. However, entering all goals manually can become a bit tedious, especially if you have your goals already sitting in an Excel file. This is often the case for example with Budget figures. In … 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