Combining Excel files on multiple sharepoint folders using Power BI

[anuradha01]

Hi All

I have Excel sheets saved on multiple different sharepoint folders. They all have the same format but different data.

I need to combine data from all the Excel sheets and generate some Power Bi Reports.

To ensure the reports show the combined data from all the sheets, I created a Parameter table with the URL of all files.

Used that parameter table to invoke a function and combine the files and then used in the reports.

For your reference, I used the steps mentioned on - https://www.youtube.com/watch?v=OC5TX_xmRzk

The reason for using this approach is because I don’t want to hardcode sharepoint URLs on the report for Excel files and wanted to keep it dynamic.

I am using same approach in a different scenario as well where there are 25 google sheets entered by people on 25 different folders and I collate all data using above approach to minimise effort of creating 25 tables in Power BI and then merging them.

However since I used parameters and function to load the source data, when I am setting up refresh schedule on Power BI Service its giving me an error –“ This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed."

Is there a workaround for the same?? Your early response would be highly appreciated.”

 I even tried merging sharepoint excel files using Powerautomate, but Sharepoint connectors is a premium option so cant do that.

I also tried using power query in Excel to combine files , but for that as well to make it refresh I need to open file at least once or twice each day to make it refresh. And I can’t use scripts or macros due to organisation level restrictions.

Any suggestions/ workaround would be highly appreciated as Power BI reports are crucial to the management for the performance review.

[foodd]

If I may, my suggestion would be to shift your approach to change from Google Forms to Microsoft Forms which would store the data in Excel format.   Once that is true, you can shift handling of Excel files using the following method which would be much easier.

Magically combine Excel files From SharePoint Folder with bonus debugging tip

[anuradha01]

Any other recommendations from anyone??

[foodd]

Would it not be easier to copy all of the Workbooks to a single folder, and import from there?   Is it more practical to add such pain to this?

[anuradha01]

I can do it for my Excel files. Once its all in one SharePoint folder then how will I combine it?

 Also with 25 Google sheets, there are Google forms set up by the team owners and it might not be feasible for them to do that. So would still need a solution to that.

[danextian]

Hi @anuradha01 ,

Tested it myself and a custom function refreshed just fine  in the service so there could be other factors at play.

[anuradha01]

Hi danextian

I removed all other steps from my report . Kept just the data load from List of files stored in parameter table. It still gives same error-

Below is the parameter table file that stores all files URL

This file path is stored in parameter - p_parameter_tbl

Below is the function code

All the files are read one by one from parameter table and then function is invoked to load data in Event_Register_Parameter_Tbl

However its still showing the error when I am trying to set refresh schedule.