fnHoltWinters.pq

/* 
Function: fnHoltWinters
Description: Trying to replicate HoltWinters quantitative smoothing with Power Query.
Author: Chris Aragao

Change Log:

11.13.2023 - Initial release. Initial code was based off of this tutorial: https://www.exceldemy.com/holt-winters-exponential-smoothing-in-excel/.
It was aimed at solely duplicating the output and did so.

11.14.2023 - Updated to provide debugging output and I tested it with different periods (quarterly and monthly). I reworked some variables and different logic
and was able to get it to work for both scenarios. I will continue to test this out and optimize it as I have time. 

11.15.2023 - Addressed some bugs with seasonal index calculations, forecasts, and RMSE.

*/


(tbl as table, col as text, periods as number, alpha as number, beta as number, gamma as number, optional debug as logical) =>
  
  
  let
    // function to choose the column with amounts and convert to list dynamically
    fnChooseColToList = (t as table, c as text) =>
      let
        Source    = Table.SelectColumns(t, c),
        To_Text   = Table.TransformColumnTypes(Source, {{c, type text}}),
        To_List   = Table.ToList(To_Text),
        To_Number = List.Transform(To_List, each Number.From(_))
      in
        To_Number,

    // function to calculate the level
    fnGetLevel = (amt as number, sea as number, lvl as number, trnd as number) =>
      let
        l = alpha * (amt / sea) + (1 - alpha) * (lvl + trnd)
      in
        l,

    // function to calculate the trend
    fnGetTrend = (amt as number, sea as number, lvl as number, trnd as number) =>
      let
        t = beta * (fnGetLevel(amt, sea, lvl, trnd) - lvl) + (1 - beta) * trnd
      in
        t,


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

    // get count of base table
    t_count = Table.RowCount(tbl),

    // get num_periods
    num_periods = t_count/periods, 

    // get the amounts
    amounts = fnChooseColToList(tbl, col),

    // change the amount column to a name we can use for processing
    Change_Amount_Col = Table.RenameColumns(tbl, {col, "var"}),

    // get the the most recent period amounts 
    First_Period = List.FirstN(amounts, periods),

    // calculate initial seasonal index
    Initial_Seasonal_Index = List.Transform(First_Period, each _ / List.Average(First_Period)),

    // calculate the initial level
    Initial_Level = amounts{periods} / Initial_Seasonal_Index{0},

    // calculate the initial trend
    Initial_Trend = Initial_Level - amounts{periods-1} / List.LastN(Initial_Seasonal_Index,1){0},

    // calculate remaining seasonal index, level, and trend with records and List.Generate()
    Remaining_SLT = List.Generate(
      () => [
        row = periods,
        season = List.Combine(
          {
            Initial_Seasonal_Index,
            {gamma * (amounts{periods} / Initial_Level) + (1 - gamma) * Initial_Seasonal_Index{0}}
          }
        ),
        var = amounts{periods},
        s_val = gamma * (amounts{periods} / Initial_Level) + (1 - gamma) * Initial_Seasonal_Index{0},
        level = Initial_Level,
        trend = Initial_Trend,
        p = periods,
        i = periods,
        max = t_count,
        s_index=0
      ],
      each [i] < [max],
      each [
        row = [row] + 1,
        season = List.Combine(
          {
            [season],
            {gamma * (amounts{[i] + 1} / [level]) + (1 - gamma) * [season]{[s_index]+1}}
          }
        ),
        var= amounts{[i] + 1},
        s_val = gamma * (amounts{[i] + 1} / [level]) + (1 - gamma) * [season]{[s_index]+1},
        level = fnGetLevel(
          amounts{[i] + 1},
          gamma * (amounts{[i] + 1} / [level]) + (1 - gamma) * [season]{[s_index]+1},
          [level],
          [trend]
        ),
        trend = fnGetTrend(
          amounts{[i] + 1},
          gamma * (amounts{[i] + 1} / [level]) + (1 - gamma) * [season]{[s_index]+1},
          [level],
          [trend]
        ),
        p = [p],
        i = [i] + 1,
        max = [max],
        s_index = [s_index]+1
      ]
    ),

    // convert that to a table
    To_Table = Table.FromList(
      Remaining_SLT,
      Splitter.SplitByNothing(),
      null,
      null,
      ExtraValues.Error
    ),

    // expand the nested records
    Expanded = Table.ExpandRecordColumn(
      To_Table,
      "Column1",
      {"row", "var", "s_val", "level", "trend"},
      {"row", "var", "s_val", "level", "trend"}
    ),
    
    // set types on the expanded columns
    Set_Types = Table.TransformColumnTypes(
      Expanded,
      {
        {"row", type number},
        {"var", Currency.Type},
        {"s_val", type number},
        {"level", type number},
        {"trend", type number}
      }
    ),

    // build the header
    header = Table.SelectColumns(Change_Amount_Col, "var"),

    // add an index
    header_index = Table.AddIndexColumn(header, "row", 0, 1, Int64.Type),

    // reorder the table so index is first
    reordered = Table.ReorderColumns(header_index, {"row", "var"}),

    // add seasonal index
    add_season = Table.AddColumn(
      reordered,
      "s_val",
      each if [row] < periods then [var]/ List.Average(List.FirstN(reordered[var], periods)) else null,
      type number
    ),

    // add level
    add_level = Table.AddColumn(add_season, "level", each null, type number),

    // add trend
    add_trend = Table.AddColumn(add_level, "trend", each null, type number),

    // combine the tables so you have the two pieces together
    combine = Table.Combine({Table.FirstN(add_trend,periods), Table.LastN(Set_Types, t_count-periods)}),

    // figure out the forecasts 
    add_forecast = Table.AddColumn(
      combine,
      "forecast",
      each
        if [level]
          <> null and [trend]
          <> null and combine[level]{[row] - 1}
          <> null and combine[trend]{[row] - 1}
          <> null
        then
          (combine[level]{[row] - 1} + combine[trend]{[row] - 1}) * combine[s_val]{[row] - periods}
        else
          null,
      type number
    ),

    // determine the errors
    add_error = Table.AddColumn(
      add_forecast,
      "error",
      each if [forecast] <> null then [var] - [forecast] else null,
      type number
    ),

    // add an index column to the s_val list (converted to table) so you can join back to the base table
    s_val = Table.AddIndexColumn(
      Table.RenameColumns(Table.TransformColumnTypes(Table.FromList({1..periods}, Splitter.SplitByNothing(), null, null, ExtraValues.Error), {"Column1", type number}),{"Column1", "s_val"}),
      "Index",
      1,
      1,
      Int64.Type
    ),

    // the next few steps are just variables I used to figure out indices
    row_start = List.Max(add_error[row]) + 1,
    row_end = row_start + periods,
    row = Table.AddIndexColumn(
      Table.FromList(
        List.Transform(
          List.Generate(() => row_start, each _ < row_end, each _ + 1),
          each Text.From(_)
        ),
        null,
        type table [row = Int64.Type]
      ),
      "Index",
      1,
      1,
      Int64.Type
    ),

    // join then select columns
    row_s_val = Table.SelectColumns(
      Table.Join(row, "Index", s_val, "Index", JoinKind.Inner),
      {"row", "s_val"}
    ),

    // combine table with errors and seasonal vals
    combined = Table.Combine({add_error, row_s_val}),
    
    changed_type = Table.TransformColumnTypes(
      combined,
      {{"row", Int64.Type}, {"s_val", type number}}
    ),


    last_row_base = List.LastN(add_error[row], 1){0},
    last_quarter_base = last_row_base - (periods-1),
    first_new_row = last_row_base + 1,

    // get new forecasts
    new_forecast = List.Generate(
      () => [
        level       = changed_type[level]{last_row_base},
        next_season = changed_type[s_val]{first_new_row},
        trend       = changed_type[trend]{last_row_base},
        prev_season = changed_type[s_val]{first_new_row - periods},
        prev_row    = last_row_base,
        curr_row    = first_new_row,
        prev_s_row  = first_new_row - periods,
        i           = first_new_row,
        max         = List.Max(changed_type[row]),
        forecast    = (level + next_season * trend) * prev_season
      ],
      each [i] <= [max],
      each [
        level = [level],
        next_season = changed_type[s_val]{[curr_row] + 1},
        trend = [trend],
        prev_season = changed_type[s_val]{[curr_row] - (periods-1)},
        prev_row = [prev_row] + 1,
        curr_row = [curr_row] + 1,
        prev_s_row = [prev_s_row] + 1,
        i = [i] + 1,
        max = [max],
        forecast = (
          ([level]
            + changed_type[s_val]{[curr_row] + 1}
           * [trend])
            * changed_type[s_val]{[curr_row] - (periods-1)}
        )
      ]
    ),

    // expand and select columns
    forecast_to_table = Table.ExpandRecordColumn(
      Table.FromList(new_forecast, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      "Column1",
      {"i", "forecast"},
      {"i", "forecast"}
    ),

    // set up another join
    set_up_join_types = Table.TransformColumnTypes(
      forecast_to_table,
      {{"forecast", Currency.Type}, {"i", Int64.Type}}
    ),

    // rename the columns so you don't get an error when you join
    new_f_name = Table.RenameColumns(
      set_up_join_types,
      {{"i", "n_row"}, {"forecast", "new forecast"}}
    ),

    // figure out RMSE
    RMSE =
      let
        errors = List.Select(changed_type[error], each _ <> null)
      in
        Number.Sqrt(
          List.Sum(List.Transform(errors, each Number.Power(_, 2)))
            / List.Count(errors)
        ),

    // make the join
    join_new_forecast = Table.Join(changed_type, "row", new_f_name, "n_row", JoinKind.LeftOuter),
    
    // coalesce forecast columnns with a merge
    merge_on_row = Table.CombineColumns(
      Table.TransformColumnTypes(
        join_new_forecast,
        {{"forecast", type text}, {"new forecast", type text}},
        "en-US"
      ),
      {"forecast", "new forecast"},
      Combiner.CombineTextByDelimiter("", QuoteStyle.None),
      "forecast"
    ),

    // reorder the columns
    reorder = Table.ReorderColumns(
      merge_on_row,
      {"row", "var", "s_val", "level", "trend", "forecast", "error", "n_row"}
    ),

    // select which columns you need
    remove_new_fcast_i = Table.SelectColumns(
      reorder,
      {"row", "var", "s_val", "level", "trend", "forecast", "error"}
    ),

    // make sure forecast is in currency type
    forecast_to_currency = Table.RenameColumns(Table.TransformColumnTypes(
      remove_new_fcast_i,
      {{"forecast", Currency.Type}}
    ), {"var", col}),

    //output as a record
    output = [forecasts = forecast_to_currency, rmse = RMSE],

    // debug output
    debug_output = [
                      1 = t_count,
                      2 = num_periods,
                      3 = amounts,
                      4 = Change_Amount_Col,
                      5 = First_Period,
                      6 = Initial_Seasonal_Index,
                      7 = Initial_Level,
                      8 = Initial_Trend,
                      9 = Remaining_SLT,
                      10 = To_Table,
                      11 = Expanded,
                      12 = Set_Types,
                      13 = header,
                      14 = header_index,
                      15 = reordered,
                      16 = add_season,
                      17 = add_level,
                      18 = add_trend,
                      19 = combine,
                      20 = add_forecast,
                      21 = add_error,
                      22 = s_val,
                      23 = row_start,
                      24 = row_end,
                      25 = row,
                      26 = row_s_val,
                      27 = combined,
                      28 = changed_type,
                      29 = last_row_base,
                      30 = last_quarter_base,
                      31 = first_new_row,
                      32 = new_forecast,
                      33 = forecast_to_table,
                      34 = set_up_join_types,
                      35 = new_f_name,
                      36 = RMSE,
                      37 = join_new_forecast,
                      38 = merge_on_row,
                      39 = reorder,
                      40 = remove_new_fcast_i,
                      41 = forecast_to_currency ,
                      42 = output
                  ]

  in

if bug = true then debug_output else output