Forecasting with HoltWinters and Power Query

I’ve had to use the HoltWinters algorithm to figure out forecasting for dollar figures at work. Power BI (since it was called Power View) has the ability to provide forecasting (I believe it is in this Microsoft Learn course).

I had implemented the initial version of my report with this feature in Power BI. However, I wanted to be able to actually see the numbers and understand it more. I was able to implement this by calling R, but I also wanted to be able to refresh (because we don’t have a personal gateway setup where I work).

Greg Deckler took a valiant crack at this after being requested by someone to try to figure this out with DAX. 71 tables later, he was able to do it, but ay yi yi 71 tables! (Still impressed by the fact he was able to figure it out).

Could Power Query do it? After spending a few days on it myself, the answer is: probably.

I found this article online and decided to take a crack with Power Query and it appears that I was able to reproduce the output.

The function takes the following parameters:

1. Table where the amounts are.
2. Name of the amounts column as a text value.
3. Periods represented as a number (basically how many rows constitute a period).
4. alpha param for HoltWinters.
5. beta param for HoltWinters.
6. gamma param for HoltWinters.

As you would expect, it ended up being really complex. I found myself going to PowerQuery.How a lot for ideas. One that saved my bacon was using List.Generate() with records. That allowed me to generate multiple values at once and determine things like counter variables and indices.

Caveats:

1. I need to probably clean this up, but the “Hello World” aspect of does it reproduce what the article shows is done.
2. I will have to work on this and test with other scenarios (variable periods, etc).
3. As I test this at work, I will update the code.

Here is a gist with the code:

https://gist.github.com/cbaragao/4b4a60e7cc14b10bb9e74aef7d06523c