Hi @MStark171 ,
You can do that without the use of Power Automate or you can use inside Power Automate. If this was my project, I would do outside of Power Automate unless there was a process that required it.
So essentially open a blank workbook and Get Data from folder. Here you can combine all the files from a folder. You can also do this from other folders, etc, etc, etc. and then you can easily combine all excel sheets into a single table of data to use in automation projects.
Now if you need to do this in automation, again ask yourself why you need it, you can get data with dataflows and do this in the cloud. Then when you combine sheets you can save the output in a dataverse table and use in automation.
Again, unless required, my preference is to combine manually with Power Query since you are working with files from the past; there's an assumption they are not changing.
Another option to consider is write quick automation that copies all sheets into a temp folder and then simply process that folder you get data > folder > combine ....
You have options and you should be able to accomplish this with Power Query directly from Excel or Dataflows in Dataverse.
