27 June 2020

Moving Average in Excel Power Query

Here's a general-purpose Power Query 'rolling' function that can be used to make a rolling averages / moving average, or any other aggregate function such as a rolling standard deviation, etc.

A self-contained example of a four-item moving average over a list of the numbers 1 to 10:

let Rolling = (list, n, fn) =>
  let
    buffered = List.Buffer(list)
  in
    List.Transform(
      List.Positions(buffered),
      each
        if _<n-1
        then null
        else fn(List.Range(buffered, _-n+1, n))
    )
let
  result = Rolling({1..10}, 3, List.Sum)
in
  result

Alternatively, to make as a reusable function:

  1. Select the Data tab
  2. Click Get Data > From Other Sources > Blank Query
  3. In the Power Query Editor window, type the name Rolling into the top-right Name box
  4. Open Advanced Editor (on the Home tab)
  5. Enter the following query:
(list, n, fn) =>
  let
    buffered = List.Buffer(list)
  in
    List.Transform(
      List.Positions(buffered),
      each
        if _ < n-1
        then null
        else fn(List.Range(buffered, _-n+1, n))
    )

The Rolling function should now be defined, and usable in other queries, such as:
=Rolling( {1..10}, 5, List.StandardDeviation )

for a 5 day rolling standard deviation over the numbers 1 to 10.




No comments: