Data loading from excel in SharePoint site is taking more time

[mbm]

Hi Experts,

I'm using an excel from SharePoint site as a data source to Power BI. The SharePoint site has 100K plus files in it. While adding this specific file to Power BI desktop, I have to give the root directory, search the file from the whole site by sorting and add it to my report. I did it with sorting because I couldn't find the file with name. Not sure if this is the correct way to do it. 

The problem is: It is taking more than two hours to load the data (my excel has less than 100 rows in it).

Is there any better way or right way to do this. Please find the attachment to see the applied steps on the file.

P.S. I have used SharePoint Folder as the data source type.

[Vijay_A_Verma · Accepted solution]

Sorting Rows is the killer step and we should try to avoid it as much as possible. Also using sharepoint folder connector is slower than accessing a single file on sharepoint directly.

Since this is a case of a single file only and I presume finding it is not rule based like all files created today / all files starting with alphabet A, the best bet would be to feed the path of file into PQ rather than feeding the folder path and then trying to locate the file.

Open Excel file in Sharepoint - File - Info - Copy path: This would give you the path which you need to feed into PQ. In PQ, you will need to use Get Data - From other sources - From Web

Let me know whether this is feasible for you or not. If not, I would like to know the logic which you are utilizing to locate the file then we can start thinking about how to avoid sorting.