Data flow using sharepoint folders

[DomantasK]

Hey,

My goal is to make an automated PBI report, which would update (including newly uploaded data) every time a new file is uploaded to a specified sharepoint folder. 

I've already made the report in PBI desktop, which uses the .xls files in sharepoint as data source, however, every time the new file is uploaded to the folder, I need to make a new query and add it to the report manually. 

So what tools/functions/knowledge should I use in order to make it automated? 
I was looking to power automate app, however, I haven't found any pre-made templates that would fit my needs.  

[Anonymous · Accepted solution]

Hi  @DomantasK ,

When you connect to Sharepoint, there will be a Combine operation, select this:

This will form a Transform File in Power Query, which contains some functions that can expand and append all files in the folder, but the limitation is that the field requirements in the two files are the same, otherwise an error will occur

Refer to：

Power Query SharePoint folder connector - Power Query | Microsoft Learn

When you publish the report to service, you could create a flow with Power Automate.

Add a new item to the SharePoint --> the flow will be triggered --> then will do the "Referesh dataset" action for Power BI.

https://learn.microsoft.com/en-us/power-automate/trigger-flow-powerbi-report

Best Regards,

Liu Yang

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