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
ITManuel
Responsive Resident
Responsive Resident

Consolidating data from various excel files in different sharepoint folders

Hi all,

 

I have a Power BI report in the Service for which I have the necessitiy to import data from various excel files in different subsites and folders in sharepoint. 

My question would be wether it is better to:

 

a) Directly connect Power BI to each individual excel file and consolidate the excel tables in Power Query from Power BI or

b) Create an excel file stored on the server or sharepoint which collects and consolidates the data from the excel files in sharepoint and than provides the data to Power BI via one connection only

c) ???

 

For option a) my worry is that I have to manage data source credentials for every single excel file which the Power BI is connected to. There are 20+ excel files I would need to connect to.

 

Thanks in advance

3 REPLIES 3
Anonymous
Not applicable

Hi @ITManuel 

You can create a single folder on the site then put the all related excel files to the folder, then in Power BI, when getting data, you can choose from Sharepoint folder, then combinr these excel files, you can refer to the following link.

Consolidate Multiple Excel Files in SharePoint using PowerQuery - Excelerator BI

Combining Excel Files hosted on a SharePoint folder with Power BI | Microsoft Power BI Blog | Micros...

 

Best Regards!

Yolo Zhu

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

Hi,

 

any further ideas in relation to the topic?

 

I was trying to set a separate Power BI report in the service which combines the files from the different Sharepoint folders, but each Sharepoint folder will need its dedicated Data gateway connection in the service in order to allow scheduled refresh to run, i do not want to have a connection for each SharpePoint folder to maintain. I also tried Power Automate, but wasn't successful so far.

 

Any help or ideas would be much appreciated.

 

Thanks & Br

Hi @Anonymous ,

 

the excel files will need to stay in different folders, since there are X projects and X folder structures, one for each project. 

 

Br

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.