Custom Column to Detect Past 3 Months and Calculate Average in Power Query

[programsam]

Greetings, I have a file that updates monthly and when a new month starts, a column is added dynamically. I'm trying to find a way to have Power Query detect the prior three months and then average them out. I cannot do this by column position alone as every month a new column will be added to the right.

In the image below, I'm trying to have it detect May, June and July 2023 and average out the values in the columns.

 

[programsam]

For some reason I cannot edit the original post but ideally, I'd like to have a 3 month average as well as an average based on the # of days within the 3 month period.

 

[JEC]

I put the specific months in my language to test.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    col = List.Accumulate({-1,-2,-3}, {}, (s,c) => let dt = Date.AddMonths(Date.From(DateTime.LocalNow()),c) in  s & {Text.Proper(Date.MonthName(dt) & " " & Text.From(Date.Year(dt)))}),
    delCols = Table.SelectColumns(Source,col),
    result = Table.AddColumn(delCols, "Average", each List.Average(Record.FieldValues(_)))
in
    result

Book1
	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	February 2022	March 2022	February 2023	February 2024	March 2023	April 2023	Mei 2023	Juni 2023	Juli 2023	MTD
2	1	2	3		3	4	4	5	6	7
3
4
5
6	Juli 2023	Juni 2023	Mei 2023	Average
7	6	5	4	5
8
Sheet1

 

[JEC]

Or you could just add the average at the end

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cols = List.Accumulate({-1,-2,-3}, {}, (s,c) => let dt = Date.AddMonths(Date.From(DateTime.LocalNow()),c) in  s & {Text.Proper(Date.MonthName(dt) & " " & Text.From(Date.Year(dt)))}),
    result = Table.AddColumn(Source, "Average", (r)=> List.Average(List.Transform(cols, each Record.Field(r,_))))
in
    result

 

[programsam]

result = Table.AddColumn(delCols, "Average", each List.Average(Record.FieldValues(_)))

@JEC, thank you for the response. Any idea why I might be getting this error?

 

[programsam]

Or you could just add the average at the end

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cols = List.Accumulate({-1,-2,-3}, {}, (s,c) => let dt = Date.AddMonths(Date.From(DateTime.LocalNow()),c) in  s & {Text.Proper(Date.MonthName(dt) & " " & Text.From(Date.Year(dt)))}),
    result = Table.AddColumn(Source, "Average", (r)=> List.Average(List.Transform(cols, each Record.Field(r,_))))
in
    result

this one worked without issue, thank you!

 

[JEC]

You're welcome!