Excel Avg Multiple Workbooks

[MikeCritt]

Hello Group

Looking for a little help. I have a folder with one workbook for everyday of the year. Starting in 2019-01-01 to today 2024-09-12.
Each workbook has a column for the date/time (Column B) and another (Column D) with the data. Starts at Row 7 and goes to row 2887
(Sheet name in each workbook is called template)

I would like to take the date from each workbook (above one would be 2019-01-01) into one column and the average of row's D7:D2887 in another column.
Something like this:

Been trying power query but haven't had much success.

Thanks in advance

 

[MikeCritt]

Trying to do vlookup below is what I have been trying:

=VLOOKUP (AVERAGE '[Test[2019-01-01.xlsx]Template'!$D$7:$D$2887)