Combining Excel files on multiple sharepoint folders using Power BI

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.