Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mbm
Frequent Visitor

Data loading from excel in SharePoint site is taking more time

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.

mbm_0-1648971937650.png

 

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

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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. 

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

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. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.