Calculate Average by month for mass data

[Mjolnir07]

Hello everyone, I used to be an intermediate bordering on advanced excel user about ten years ago, but I haven't had to use my hard earned knowledge
since then and POOF! It's like it was never there. I know there's a simple solution to this that doesn't involve dragging columns but I'm drawing a blank

So I have about three hundred databases set up like the example below, with the date on the left and that day's value to the right. Three cells over somewhere
on the sheet are monthly average data. The problem I have is that these databases are each 11 years worth of cells in this form, and along the way the various
users who have been calculating the averages have messed up the ranges here in there, either by assuming that simply autofilling to the next row will tell
excel what it needs to know to calculate the next month's average, or by simply selecting the wrong ranges.

What I'm asking is, is there a faster way to gather the averages for each month then by individually typing out or drag-selecting the range for each month?
At this point I'm having to scroll past a thousand cells to locate the first date of each month and the last date, then manually enter the range for each average
and double check each cell with an average for accuracy one at a time.

The best thing I've come up with so far has been to create a table that calculates the cell ranges based upon the first cell selected using the number of days in each month
(e.g., January has 31 days, so add 31 to cell x and input to cell y, then populate cell numbers) and still manually entering the averages using those calculated numbers.

I don't know if that's a clear enough explanation of the problem.

TLDR is there a faster way to have cell lookup ranges in huge databases and outputting their averages to a designated cell, than actually having to go find those ranges and select
or enther them each by hand?

Thanks for any input!!!.

6/1/19​	7​
6/2/19​	6​
6/3/19​	3​		June 19 Average	3​
6/4/19​	4​		July 19 Average	77.80645​
6/5/19​	5​		Aug 19 Average	70.12903​
6/6/19​	6​
6/7/19​	7​
6/8/19​	4​
6/9/19​	9​
6/10/19​	1​
6/11/19​	11​
6/12/19​	3​
6/13/19​	13​
6/14/19​	34​
6/15/19​	3​
6/16/19​	3​
6/17/19​	17​
6/18/19​	3​
6/19/19​	4​
6/20/19​	20​
6/21/19​	3​
6/22/19​	22​
6/23/19​	43​
6/24/19​	35​
6/25/19​	36​
6/26/19​	26​
6/27/19​	22​
6/28/19​	44​
6/29/19​	29​
6/30/19​	4​
7/1/19​	31​
7/2/19​	33​
7/3/19​	4​
7/4/19​	34​
7/5/19​	4​
7/6/19​	5​
7/7/19​	37​
7/8/19​	38​
7/9/19​	5​
7/10/19​	40​
7/11/19​	41​
7/12/19​	22​
7/13/19​	41​
7/14/19​	44​
7/15/19​	45​
7/16/19​	234​
7/17/19​	47​
7/18/19​	48​
7/19/19​	49​
7/20/19​	344​
7/21/19​	51​
7/22/19​	52​
7/23/19​	42​
7/24/19​	546​
7/25/19​	55​
7/26/19​	56​
7/27/19​	57​
7/28/19​	243​
7/29/19​	59​
7/30/19​	44​
7/31/19​	61​
8/1/19​	4​
8/2/19​	63​
8/3/19​	64​
8/4/19​	44​
8/5/19​	66​
8/6/19​	4​
8/7/19​	4​
8/8/19​	69​
8/9/19​	4​
8/10/19​	56​
8/11/19​	72​
8/12/19​	2​
8/13/19​	74​
8/14/19​	43​
8/15/19​	76​
8/16/19​	77​
8/17/19​	346​
8/18/19​	79​
8/19/19​	79​
8/20/19​	79​
8/21/19​	79​
8/22/19​	79​
8/23/19​	79​
8/24/19​	79​
8/25/19​	79​
8/26/19​	79​
8/27/19​	79​
8/28/19​	79​
8/29/19​	79​
8/30/19​	79​
8/31/19​	79​

 

[sandy666]

want to know more about PQ?

• What is PowerQuery (Get&Transform)
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)

 

[sandy666]

I have about three hundred databases

files or sheets?

 

[sandy666]

the same workbook, different sheets (each for one year) with tables (the same structure)

Month	2019	2018	2020		or		Month	Year	Average		or		Year	June	July	August
August	70.12903226	14.09677419	26.5483871				June	2019	14.23333333				2018	13.5	13.67741935	14.09677419
July	77.80645161	13.67741935	25				July	2019	77.80645161				2019	14.23333333	77.80645161	70.12903226
June	14.23333333	13.5	27.23333333				August	2019	70.12903226				2020	27.23333333	25	26.5483871
							June	2018	13.5
							July	2018	13.67741935
							August	2018	14.09677419
							June	2020	27.23333333
							July	2020	25
							August	2020	26.5483871

example for the middle table

let
    Source = Excel.CurrentWorkbook(),
    Filter = Table.SelectRows(Source, each not Text.Contains([Name], "Query")),
    TSC = Table.SelectColumns(Filter,{"Content"}),
    Expand = Table.ExpandTableColumn(TSC, "Content", {"Date", "Value"}, {"Date", "Value"}),
    Month = Table.AddColumn(Expand, "Month", each Date.MonthName([Date]), type text),
    Year = Table.AddColumn(Month, "Year", each Date.Year([Date]), Int64.Type),
    Group = Table.Group(Year, {"Month", "Year"}, {{"Average", each List.Average([Value]), type number}})
in
    Group

 

[Mjolnir07]

:O

Thankyouthankyouthankyouthankyouthankyou

 

[sandy666]

WelcomeWelcomeWelcomeWelcomeWelcome

example file