Power BI ETL Capabilities

You can do it all in Power BI w/ no 3rd party tools.  Note that you won't have a "live" connection to the Excel workbooks (although you can to other types of sources), but you can't set up automated refresh multiple times a day based on your license.  To get new worksheets as they are added, assuming each worksheet is formatted the same way, you would likely use a custom function to process each worksheet and then combine all the tables (from each worksheet).

Here is a good video that should help - https://exceleratorbi.com.au/combine-all-sheets-in-a-workbook-with-power-query/

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat