Forum Discussion

cassidy's avatar
cassidy
Power Participant
5 years ago
Solved

Refresh Excel Pivots within Service

I've published an Excel Report, with a Power BI Dataset Connection, to a Premium Workspace.     The report has a Pivot Table connected to my Power BI Dataset that needs to be refreshed daily, howev...
Need Help
  • cassidy's avatar
    cassidy
    5 years ago

    Hello v-xiaotang , thanks for the reply.

     

    Part of that helped me find a solution.  

     

    Since the file is on OneDrive, if it gets refreshed, then Power BI will pick up the changes...cool.  I still need to refresh the Excel file automatically.  

     

    I added a script to the Excel Workbook to Refresh it and then created a Power Automate scheduled flow to run the Script.

     

    Goes like this:  Power Automate runs Script > Excel workbook refreshes Power BI Dataset connection and Pivots > Power BI Service picks up the Excel workbook change

     

    Here was the script I used:

     

    function main(workbook: ExcelScript.Workbook) {
      // Refresh all data connections
      workbook.refreshAllDataConnections();
    }
v-xiaotang's avatar
v-xiaotang
Community Support
5 years ago

Hi cassidy 

I guess you might need to build model in Excel firstly,

but it has a simpler way to solve this question ——> import the excel file into PowerBI Desktop, then upload this pbix file.  You will get a dataset that can set scheduled refresh.

-

besides, if you import file from Onedrive, the file will update automatically when it detects changes. so you won't find "Schedule Refresh" option of this file.

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-csv-file-onedrive

 

 

Best Regards,

Community Support Team _Tang

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

 

 

 

 

  • cassidy's avatar
    cassidy
    Power Participant
    5 years ago

    Hello v-xiaotang , thanks for the reply.

     

    Part of that helped me find a solution.  

     

    Since the file is on OneDrive, if it gets refreshed, then Power BI will pick up the changes...cool.  I still need to refresh the Excel file automatically.  

     

    I added a script to the Excel Workbook to Refresh it and then created a Power Automate scheduled flow to run the Script.

     

    Goes like this:  Power Automate runs Script > Excel workbook refreshes Power BI Dataset connection and Pivots > Power BI Service picks up the Excel workbook change

     

    Here was the script I used:

     

    function main(workbook: ExcelScript.Workbook) {
      // Refresh all data connections
      workbook.refreshAllDataConnections();
    }
    • ironpink's avatar
      ironpink
      Frequent Visitor
      4 years ago

      hi, I'm newbee

      could you please explain me where (and how) put the code?

      tx raffaele