Automatically detect new excel files in Sharepoint Folder and load them to semantic model

[Johann_32]

Hello,

we have a very specific request, in which users are storing several Excel Files (different file names, same amount of columns and same name of columns inside) per week in a sharepoint folder. This means, each of those files only differs in the rows.

Now, we know how we can merge multiple Excel files together in one table in Power BI Desktop, but this is not enough.

After the report is built and published, there should be a kind of mechanism to detect when again a new file was stored in that Sharepoint Folder, and that excel file should also be loaded into the semantic model to have newest data available in the report.

We know this is a very uncommon request, as data should be collected in one or several defined Excels, but still I would like to ask if somebody had that challenge in the past.

Thank you 

Johann

[Sergii24]

Hi @Johann_32, you can use Power Automate to obtain the desired result:

In the first step you can also detect any changes in the folder which will trigger the following actions. Delay is used to make sure that file has been properly uploaded to SharePoint and all sync has been finished. Then you refresh a semantic model. 

Have a great day! 

[Johann_32]

Thanks for the quick reply!

As far as I understand, this would help only for triggering the refresh. But it does not add completely new Excel files to the Report and Semantic Model, that have never been before in the report.

Or did I misunderstood?

[Sergii24 · Accepted solution]

You're right. To add new files to the semantic model you need to save the files that have the same structure in the same folder and use "GetData" from Sharepoint Folder. You can learn about it in this video - link