How to perform 5 year and 10 year Moving average in Power Query

[extreamsujo]

MonthName Calendar_Year Total Number Moving Average 5 Year Moving Average 10 Year
April 2000 135
April 2001 76
April 2002 33
April 2003 31
April 2004 228
April 2005 200 101
April 2006 350 114
April 2007 126 168
April 2008 102 187
April 2009 126 201
April 2010 333 181 141
April 2011 137 207 161
April 2012 121 165 167
April 2013 36 164 175
April 2014 79 151 176
April 2015 272 141 161
April 2016 282 129 168
April 2017 96 158 161
April 2018 93 153 158
April 2019 181 164 158
April 2020 64 185 163
April 2021 144 143 136
April 2022 126 116 137
April 2023 236 122 137
August 2000 66
August 2001 83
August 2002 118
August 2003 236
August 2004 117
August 2005 84 124
August 2006 151 128
August 2007 157 141
August 2008 221 149
August 2009 178 146
August 2010 171 158 141
August 2011 154 176 152
August 2012 267 176 159
August 2013 164 198 174
August 2014 249 187 166
August 2015 149 201 180
August 2016 122 197 186
August 2017 247 190 183
August 2018 160 186 192
August 2019 73 185 186
August 2020 176 150 176
August 2021 164 156 176
August 2022 275 164 177
August 2023 52 170 178

The above table is obtained from excel, how to perform 5 year and 10 year moving average in Power Query, For example the 5 year moving April 2005 is the average of (April 2000 - April 2004) and for the April 2006 is the average of (April 2001 - 2005), the same for 10 year moving average, where for August 2010 is the average (Aug 2000 - 2000) I want to replicate the above format in Power BI power query, with blanks for the ones that doesn't have previous 5 or 10 year data?

[foodd]

In your next post on the Forum, if you intend to add source data in the post, and mention that it is from Excel, 

ensure that it is able to be pasted cleanly to Excel or supply it as an Excel or CSV file.  The tendency would be to use DAX, though the request was to develop a solution using Power Query.

The same question was added to Stack Overflow today by Stack Overflow's new member SJC.

A working solution was supplied attributed to Stack Overflow member Horseyride,

 and is supplied below for the reader and transparency.

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date.MonthName", type text}, {"Calendar_Year", Int64.Type}, {"Total Number", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Date.MonthName"}, {
    {"data", each 
        let #"Added Index" = Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type),
        #"Added Custom" = Table.AddColumn(#"Added Index","fiveyear",(x)=>List.Average(Table.SelectRows(#"Added Index", each [Calendar_Year]<x[Calendar_Year] and [Calendar_Year]>=x[Calendar_Year]-5 and x[Index]>4)[Total Number])??0),
        #"Added Custom2" = Table.AddColumn(#"Added Custom","tenyear",(x)=>List.Average(Table.SelectRows(#"Added Index", each [Calendar_Year]<x[Calendar_Year] and [Calendar_Year]>=x[Calendar_Year]-10 and x[Index]>9)[Total Number])??0)
        in #"Added Custom2"
, type table [Date.MonthName=nullable text, Calendar_Year=nullable number, Total Number=nullable number, fiveyear=nullable number, tenyear=nullable number]}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Calendar_Year", "Total Number", "fiveyear", "tenyear"}, {"Calendar_Year", "Total Number", "fiveyear", "tenyear"})
in #"Expanded data"