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
resultAlternatively, to make as a reusable function:
- Select the Data tab
- Click Get Data > From Other Sources > Blank Query
- In the Power Query Editor window, type the name Rolling into the top-right Name box
- Open Advanced Editor (on the Home tab)
- 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.
