Sharepoint import folder options

[dmm3344]

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

[v-xiaotang]

Hi @dmm3344 

Thanks for reaching out to us.

please check if this video helps, Get SharePoint List Data with Power BI ... Fast - YouTube

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[dmm3344]

Thanks for the reply. Thr files are currently excel files in folders on sharepoint. 

Its about 3000 files and growing. Is there a way to covert it easily to a sharepoint list?