Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Refresh Excel Workbook published automatically on demand

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.

Need Help
Tips and Tricks

8 Replies

  • otravers's avatar
    otravers
    Community Champion
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      otravers
      Community Champion
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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 ?

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    3 years ago

    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.