Forum Discussion
Get Average of Last X Values
- 1 year ago
Hi ptmuldoon , is this what you are looking for? I'll attach the images of the Source, Output and M code. Thanks. Let me if I understood your query correctly.
Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Row Labels", type any}, {"Differentials", type number}}),
Indexing = Table.AddIndexColumn(#"Changed Type","Index",0,1),
Numbers = Table.TransformColumns(Indexing,{"Index", each {_ - 6.._}}),
Negative = Table.TransformColumns(Numbers,{"Index", each List.Select(_, each _ >= 0)}),
Records = Table.TransformColumns(Negative,{"Index", each List.Transform(_, each #"Changed Type"{_})}),
Average = Table.TransformColumns(Records, {"Index",each if Table.RowCount(Table.FromRecords(_)) = 7 then List.Average(Table.FromRecords(_)[Differentials]) else null}),
Cols = Table.RenameColumns(Average,{{"Index", "Rolling Average"}})
in
Cols(I have hardcoded the Rolling Average value that is 7, but that can added as a new step for it).
Hi ptmuldoon , is this what you are looking for? I'll attach the images of the Source, Output and M code. Thanks. Let me if I understood your query correctly.
Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Row Labels", type any}, {"Differentials", type number}}),
Indexing = Table.AddIndexColumn(#"Changed Type","Index",0,1),
Numbers = Table.TransformColumns(Indexing,{"Index", each {_ - 6.._}}),
Negative = Table.TransformColumns(Numbers,{"Index", each List.Select(_, each _ >= 0)}),
Records = Table.TransformColumns(Negative,{"Index", each List.Transform(_, each #"Changed Type"{_})}),
Average = Table.TransformColumns(Records, {"Index",each if Table.RowCount(Table.FromRecords(_)) = 7 then List.Average(Table.FromRecords(_)[Differentials]) else null}),
Cols = Table.RenameColumns(Average,{{"Index", "Rolling Average"}})
in
Cols
(I have hardcoded the Rolling Average value that is 7, but that can added as a new step for it).