Refresh Excel Workbook published automatically on demand

[Anonymous]

Hello experts,

We have a CSV file present on sharepoint. We have an excel file uploaded on Power BI Service which is connected to that CSV through get data approach.

We want an automated approach where we can refresh the excel on demand as and when csv file is been updated.

For eg. CSV file is been updated on sharepoint so we want our excel to get updated with the csv data in automated approach on demand.

[v-henryk-mstf]

Hi @Anonymous ,

There are a few ways you can refresh the Excel data connected to a CSV file on Sharepoint in an automated way:

• One option is to use the Power BI API to refresh the data. You can use the API to trigger a refresh of the data in your Power BI dataset, which will update the Excel file with the latest data from the CSV.
• Another option is to use Power Automate (previously known as Microsoft Flow) to set up a flow that will trigger the data refresh when the CSV file is updated. You can set up a flow that monitors the Sharepoint folder where the CSV file is located, and when it detects a change to the file, it will trigger the data refresh in Power BI.
• Alternatively, you can use the Power BI REST API to refresh the dataset. You can use the API to refresh the dataset on demand, or you can set up a scheduled refresh to regularly update the data at a specific time.

It's important to note that in order to use the Power BI API or Power BI REST API, you will need to have a Power BI Pro license. If you don't have a Power BI Pro license, you can still use Power Automate to trigger a data refresh, but you will need to have a Power Automate license.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[Anonymous]

Hello @v-henryk-mstf 

Thank you so much for your suggestions.

We can refresh the "dataset" from APIs in Power BI. But we have no dataset created for the excel in Power BI Service because we have uploaded a excel file in Power BI Service which is connected to sharepoint csv and in Service there is no dataset created for this excel.

Can you please guide us further ?

Thank You!

[otravers]

Have a look at this new capability:

https://powerbi.microsoft.com/en-us/blog/announcing-connected-excel-tables-from-power-bi/

[Anonymous]

Hello @otravers ,

Thank you for the suggestion.

If we go with analyse in excel it will create a pivot table. We don't want a pivot table.

We want to get our workbook refreshed automatically on demand.

[otravers]

You'll have to test whether a Power Query refresh can be automated via Excel OfficeScripts, last time I checked that wasn't yet the case. If it doesn't work, you want to try Power Automate Desktop.

[Anonymous]

Hello @otravers ,

Thank you so much for your suggestion.

I tried Power Query Refresh through Office scripts but it is not working yet and Power Automate Desktop flow is not in our context.

Do we have any other way to achieve it ?

[otravers]

I'd try generating an Excel table using Power Automate but that might imply deleting/recreating the workbook rather than "refreshing" it.

[otravers]

You can do this in a roundabout way, using Power Automate if you go via a Power BI dataflow or dataset refresh, then Analyze in Excel. I'm not sure Power Query refreshes in Excel can be automated, last time I checked it wasn't working via OfficeScripts but I think they planned to add support for it.