Consolidating data from various excel files in different sharepoint folders

[ITManuel]

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

[Anonymous]

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.

[ITManuel]

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

[ITManuel]

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