HoltWinters Revisions and Debugging Idea

First thing, I made edits to the HoltWinters Power Query Function I have been working on. For the first iteration, I was mainly looking to duplicate the output of the tutorial I found online. Having accomplished that, my next thought was to enhance it and continue optimizing it over time.

One thing I realized right off the bat was that the initial tutorial was looking at quarterly projections. I thought, ok, well that may not be the projections we want in all circumstances. The day may be at a monthly grain, for instance.

I started to rethink and rework the function to be a little more dynamic and so far have tested a monthly dataset and the quarterly one. The code broke in places and I was spending a lot of time going back and forth and debugging. It was a little painful and anyone that has probably felt this at some point.

One thing that helped was to take all of the steps and create a way to debug efficiently by utilizing records. Brian Julius has a great video on how to debug M and I used some of this logic, but had to extend it to the 40+ steps that were in my code.

For my debugging solution, I first used the optional parameter keyword to set a parameter named debug as a logical data type:

(tbl as table, col as text, periods as number, alpha as number, beta as number, gamma as number, optional debug as logical) =>

This allows me to debug only when necessary. If I omit that parameter, the code will return the output. Next, early on I establish the default value for this parameter:

    // set debug var
    bug = if debug = null then false else debug,

This makes sure if it is omitted that the value will be null and will return the output. Next, towards the end of the code I just made a record with numbers as the record id set them equal to each step:

// debug output - it was really long, only include some.
    debug_output = [
                      1 = t_count,
                      2 = num_periods,
                      3 = amounts,
                      ...
                      40 = remove_new_fcast_i,
                      41 = forecast_to_currency ,
                      42 = output
                  ]

Lastly, I included an if statement in the return where if debug was true, then the function should return the debugging record. If it was false, the function should return the output.

This was helpful for me because I could click through the record and visually see where things started to break really quickly. When you have 42 steps to debug, any time saved with debugging can make your life a lot easier.

I am not sure that this is necessarily a novel idea, but I could see myself using it again.