Forum Discussion
Power Automate Refresh Power BI Report when someone makes a change in sharpeoint excel file
I have a power BI report which the data source is coming from a folder in SharePoint and it's an excel file which multiple people contribute to. I want my Power BI report refreshed automatically every time someone makes a change in the excel file without me having to refresh power bi every time manually.
Any suggestions?
Also, how do I know if it works to test? There have been updates in the file, however Power BI hasn't reflected them with the above screenshots for my flow...
Please help!!
Thank you!!!
Hi lennyt99
I could not see any screenshots.
Also if you are using Power BI Pro you can only refresh a dataset a max of 8 times a day. So if there are say 8 updates by midday the rest of the day there will be no more updates.
With regards to using Power Automate to refresh the dataset when an Excel file is updated, you might be able to run a script in order to make this change
Hi lennyt99 - Power Automate includes a trigger which starts a flow when a file is modified. However, I don't think you should use this automated option. This is because the file can be modified too many times due to autosave and number of users.
Instead, I recommend creating an "on-demand" flow within the SharePoint library. Here is an example that triggers an approval workflow - Run Power Automate flow from SharePoint - For a Selected Item trigger - YouTube. This will require a User to trigger the flow after they have finished updating the file (note in a Shared Capacity the refresh may only occur 8 times during a 24 hour period). You should still schedule the refresh for at least once per day to capture when the flow is not triggerred manually.
If you want to check this is working, you could test 2 ways:- Pick up the modified date from the SharePoint file in a Power Query and include the value in the data model
- Pick up a datetime cell from the excel file. This should be updated when the file recalculates upon saving.
You could add Power Automate steps to query the current datetime before the refresh and during the refresh in loop to notify people when the datetime changes.
3 Replies
Hi lennyt99
I could not see any screenshots.
Also if you are using Power BI Pro you can only refresh a dataset a max of 8 times a day. So if there are say 8 updates by midday the rest of the day there will be no more updates.
With regards to using Power Automate to refresh the dataset when an Excel file is updated, you might be able to run a script in order to make this change
What do I put in for script?
Hi lennyt99 - Power Automate includes a trigger which starts a flow when a file is modified. However, I don't think you should use this automated option. This is because the file can be modified too many times due to autosave and number of users.
Instead, I recommend creating an "on-demand" flow within the SharePoint library. Here is an example that triggers an approval workflow - Run Power Automate flow from SharePoint - For a Selected Item trigger - YouTube. This will require a User to trigger the flow after they have finished updating the file (note in a Shared Capacity the refresh may only occur 8 times during a 24 hour period). You should still schedule the refresh for at least once per day to capture when the flow is not triggerred manually.
If you want to check this is working, you could test 2 ways:- Pick up the modified date from the SharePoint file in a Power Query and include the value in the data model
- Pick up a datetime cell from the excel file. This should be updated when the file recalculates upon saving.
You could add Power Automate steps to query the current datetime before the refresh and during the refresh in loop to notify people when the datetime changes.
