Refresh Excel Pivots within Service

[cassidy]

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, however I don't see a function while viewing the report in Service to Refresh the table. 

• Before the very recent Excel Online/BI update, I'd always get the "can't refresh from a Power BI source" error.  That's now gone, hence why I'm attempting this.

I would prefer the "Schedule Refresh" option that was noted when I imported the book from One Drive, but not seeing that anywhere either.

The workspace claims I don't have any Workbooks with a Data Model.

[v-xiaotang]

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 · Accepted solution]

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]

hi, I'm newbee

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

tx raffaele