Triple Exponential Moving Average in Power Query M

[jsflint]

Hello!  I need some help creating a Triple Exponential Moving Average in Power Query M.  I already know how to do so in Excel, and I recently received some excellent help from @Fowmy creating an exponential moving average in Query.  I've tried repeating "Step3" in the M code below, however, the first value should not be the first value in the "EMA" column.  It's supposed to be row #15 of the EMA column as the first value -> 0.483628093 (see screenshot).  This EMA would be column "EMA2".  The third column would be "EMA3", and it's first value would be row #22 (7 day EMAs).  With these 3 EMAs, the final column would be TEMA, and this would should be simple as the formula is (3*EMA1) - (3*EMA2) + EMA3.  I've also included a link to a sample Excel spreadsheet demonstrating a TEMA, and how it's supposed to be calculated.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xyz\Desktop\ematest.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"rand", type number}}),
    Step1 = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
  Step2 = Table.AddColumn(
    Step1,
    "Average",
    each if [Index] > 6 then List.Average(List.Range(Step1[rand], _[Index] - 7, 7)) else null
  , type number),
  Step3 = Table.AddColumn(
    Step2,
    "EMA",
    each
      if [Index] > 6 then
        let
          start = List.First(List.RemoveNulls(Step2[Average])),
          vlist = List.Range(Step2[rand], 8, _[Index] - 7),
          acc = List.Accumulate(
            vlist,
            start,
            (state, current) => (current - state) * (2 / (1 + 7)) + state
          )
        in
          if [Index] = 7 then start else acc
      else
        null,
  type number ),
  Step4 = Table.AddColumn(
    Step3,
    "EMA2",
    each
      if [Index] > 13 then
        let
          start = List.First(List.RemoveNulls(Step3[EMA])),
          vlist = List.Range(Step3[EMA], 15, _[Index] - 7),
          acc = List.Accumulate(
            vlist,
            start,
            (state, current) => (current - state) * (2 / (1 + 7)) + state
          )
        in
          if [Index] = 14 then start else acc
      else
        null,
  type number )
in
    Step4

ema.xlsx

Thank you for the help!

[Anonymous]

Hi @jsflint ,

The logic seems to be a little complex that may reduce Power Query performance.

In this case, It's suggested to connect to the excel file directly.

Refer to:

Best practices when working with Power Query | Microsoft Docs

Watch Your Steps! Power Query Performance Caution for Power BI

A comprehensive guide to Power BI performance tuning - SQLGene Training

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jsflint]

I'm trying to avoid doing the calculations in Excel, then linking to it because it will then require me to manually update the spreadsheet in order to update the report.  I need the live report to connect, then have Power Query make the calculations for the EMAs and TEMA.