Forum Discussion
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, 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.
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 connectionsworkbook.refreshAllDataConnections();}
3 Replies
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.
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 connectionsworkbook.refreshAllDataConnections();}hi, I'm newbee
could you please explain me where (and how) put the code?
tx raffaele
