Share via

How to use power automate to refresh power query in Excel

Labuson,Michelle_Mae (GBS CNW) BI-PH-M 0 Reputation points
2025-11-27T14:17:16.31+00:00

I have created a masterfile excel where I combine 2 different excel data using power query editor. Now I want to automate the refreshing of table in the masterdata excel then save a copy of it in a folder. So, I used a script with function to refresh the power query table, and I transformed it into a button so whenever I clicked it, it will refresh the data in power query and then reflecting to the summary table in my masterfile excel. Now to further lessen the manual work, I want to utilize power automate using the action, "run script" feature to automate the refresh of power Query. But I have been encountering errors in the run, my masterfile table is not updating even "run script" action is successful upon testing, I also had an error in copying the masterdata file, "the file is open in a username". Is there a way to automate this? is Power automate or office script the suitable venue to execute what I want to achieve my output?

Moved from: Community Center | Discuss the Q&A site | Get started on Q&A

Microsoft 365 and Office | Excel | Other | Other
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2025-11-27T14:17:27.9933333+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    To automate the refreshing of Power Query in Excel using Power Automate, you can utilize the "Run script" action effectively. However, there are a few considerations to ensure it works smoothly:

    1. Ensure the Script is Correct: Make sure that the Office Script you created for refreshing the Power Query table is functioning correctly when run manually. If it works manually but not through Power Automate, there may be issues with how the script is set up or how Power Automate is configured to call it.
    2. File Access Issues: The error message indicating that the file is open by another user suggests that the Excel file might be locked. Ensure that no other instances of the file are open when you attempt to run the script through Power Automate. You may need to close all instances or ensure that the file is not being accessed by another process.
    3. Using Power Automate: In Power Automate, after setting up the flow to run the script, you can add additional actions to save a copy of the master file. Make sure to use the appropriate connectors (like OneDrive or SharePoint) to handle file operations.
    4. Testing the Flow: Test the flow step by step to identify where it might be failing. You can add logging or notifications in your flow to help diagnose issues.
    5. Licensing: Ensure that you have the necessary licensing for using Power Automate with Office Scripts, as some features may be restricted based on your subscription.

    If you continue to face issues, reviewing the specific error messages in Power Automate can provide more insight into what might be going wrong.

    References:

    0 comments
  2. Noel Macadangdang 7,765 Reputation points Independent Advisor
    2025-11-27T16:46:18.2266667+00:00

    Hi Labuson,

    Thank you for sharing your question. I understand what you're trying to do, refresh your Power Query, update a table, and save a copy. I am here to help.

    I see your script runs fine locally, but Power Automate says it's working when it's not updating the main table. Plus, you're getting that file is open error when you try to copy. It sounds like you are having a hard time automating your process.

    When Power Automate runs a script, refreshing isn't fully supported. The workbook.refreshAllDataConnections() thing mostly works for Power BI stuff, not for other Power Query sources. That's why you see success but nothing changes.

    Here's how to fix this.

    Easiest way Use Excel online and its built-in Script Scheduling (forget Power Automate for now). Set your script to run at certain times. Have it update the summary table right away. If you HAVE to copy the file, then use Power Automate just for copying, and make it retry until the file is unlocked.

    If your data is Power BI Keep your current setup. The script refresh thing does work for Power BI in Power Automate. After the script runs, use Excel Online actions to save or export.

     

    I hope this answer is helpful, if you have any questions, feel free to reply.

     

    Best Regards,

    Noel

     

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.