Sharepoint import folder options

I’m having issues linking a power query to a share point data source efficiently and the solutions I found on here and youtube seem to have limitations.

Im collating multiple excel sheets which are several layers deep in a Sharepoint folder.

Here is what I tried

1. Using Sharepoint.File and filtering the files paths to the required folders however. This takes too long to refresh the query as the Sharepoint contains millions of files.

2. To avoid running through each file i started Using Sharepoint.Tables and Sharepoint.Contents then drill down into each table to reach the required folder. I get 2 issues with these methods. Firstly, there seems to be a limitation where records after 60k are not pulled in so the im missing the required source folder( i believe this was with Sharepoint.Table). Secondly, I am seem runing into authentication issues. I have owner permission to the required source but some of the other folders i am restricted from. This seems to stop the Sharepoint.Contents query dead in its track as it is running through each table.

Anyone have any ideas or thoughts? Thanks