I believe that Ruth (Curbal) has something that would fit what you are after.
A cleaner way to import files from a SharePoint folder with a Power Query function - YouTube
Also Melissa has something with regard to bringing in multiple tabs from an Excel sheet
Append A Vast Number Of Excel Sheets In Power BI - YouTube
Hope this is what you are after.
Regards,
DavidH