You’re offline. This is a read only version of the page.
48
Hello,
I use Power automate to refresh an excel table that is connected to Power BI every day.
This is via a 'Run Script' prompt that refers to a script containing Workbook.refreshAllDataConnections().
As of wednesday may 10th I receive the below error message:
Office JS error: Line 4: Workbook refreshAllDataConnections: There was an internal error while processing the request. clientRequestId: a7b48e93-4b3b-4db6-93a2-56378d34dd67
-> the script does work within excel (or excel online) itself.
I had priorly had a similar problem as now. This was then resolved, but I think the issue has come back.
Do others have this issue as well?
EDIT:
AS OF MONDAY 15TH MAY I HAVE NO ERROR MESSAGE ANYMORE. HOWEVER THE REFRESH SIMPLY DOESN'T PERFORM A REFRESH. THE SCRIPT DOES WORK FINE WHEN IT IS NOT CALLED FROM POWER AUTOMATE.
Thank you for the help.
Hi all,
I'm using Power Automate to run a workflow that opens an Excel workbook stored in OneDrive/SharePoint, executes a Power Query (to refresh data), and then runs an Office Script to perform some updates. The flow works as expected, but the changes seem to be kept in memory and are not saved to the actual file - meaning the workbook doesn't reflect the changes until someone manually opens the workbook.
Is there a reliable way to force the workbook to save changes after running Power Query and Office Script via Power Automate?
If anyone needs a workaround, you can use this template set up to quickly update & create / sync datasources to standard Excel tables without power query:
No. What usually happens, is someone will come in here, ask "Any updates?" and someone will come in and say "Did you check the file size?" then there is back and forth about file size because most everyone is working with tiny single sheet workbooks. Then someone says, "use powerbi," then there is back and forth about power bi, because many can't use PBI. Then someone says "it's not supposed to work" then there is back and forth about how it was working until last summer. Then the person who is from M$ will say they will look into it further and the thread goes quiet. Then someone will ask "any updates" and some will say, "did you check the file size?"
... And the circle of life of this thread begins again.
Hi,
Is there any progress? Has anyone got any solution for this issue? I'm trying to run the script to refresh an Excel file (35 kB) on Sharepoint.
I am going to go try it again. My file are on OneDrive. I will activate the script that was working before all this started and see if it works again today and report back here.
@gbdawg82 just to be clear I have been using Sharepoint to store my Excel files and not tried OneDrive. Are you sure you have the correct access on the Sharepoint site as it seems odd it would work on one and not the other?
I am setting up new flows now so everything seems to be working well again, I just hope it doesn't stop working again as I start to rely on this more and more.
@gbdawg82 the only connection method I don't have working is using DAX studio right now which is fair enough.
I tested Excel "Get data from power BI service" for you and that seems to be the same connection that is created using Anlayze in Excel so that is working for me too.
The only advice I have is to stay away from using accessing measures directly by building a summarized flat table in the power BI report then pull this directly so no calculations need to happen during the refresh. This caused problems for me in the past with a large data set (700k records with multiple elements attached) but I resolved it by creating a table that already has the calculations done already as static numbers. The calculations are done when the dataset is refreshed ready for me to pull it into Excel.
Share
Report
All responses (
Answers (
