Get data from multiple excel files with multiple sheets

Hi there,

Fairly new to PowerBI and looking for some help.

I have around 60 excel files structured as follows:

Filename: Apr 20 Sheets: 2017, 2018, 2019, 2020

Filename: Apr 19 Sheets: 2017, 2018, 2019

Filename: Apr 18 Sheets: 2017, 2018

Filename: Apr 17 Sheets: 2017

These are point in time files.  So the "Apr 17" file incudes all data for the 2017 year asat 30/04/17.  The "Apr 19" file also contains data relating to the 2017 year, but is asat 30/04/19

Here's what i need to do;

1. Combine all files and sheets into a single table. 
2. I want to add a new column called year, this should take the sheetname as the year and populate that value for each record.
3. I want to add a new column called Date_Loaded which sets the value to be the last day of the month, it should take the year in the filename.  E.g 30/04/17 for the "Apr 17" file, 30/04/18 for the "Apr 18" file etc
4. When a new file is dropped into the folder, it should repeat the steps above, combining the data and adding the custom columns.

Help would be much appreciated.

Thanks