Power Query Problems

How to Automate Excel Power Query Refresh for SharePoint Files

Chanuka Geekiyanage Oct 19, 2026 868 views

Question details

The user needs a method to automate the 'Refresh All' operation for large Excel workbooks stored in SharePoint and Microsoft 365 that contain heavy Power Query connections.

How to Automate Excel Power Query Refresh for SharePoint Files

Product: Microsoft Excel, Power Automate
Device & OS: not provided
Scenario: Setting up an automated data refresh for heavy Excel files with external links and Power Query connections stored in a cloud environment.
Observed behavior: Excel for the web lacks a universal action to reliably auto-refresh all embedded Power Queries, requiring workarounds like local sync desktop flows or cloud flow connectors.

Before you start

Ensure you have an active Microsoft Power Automate license and that the OneDrive sync client is properly configured on your computer if you plan to use desktop automation.

Solution 1Recommended

Use Power Automate Desktop with Local Sync

Best for automating standard Power Query refreshes by leveraging the desktop version of Excel and local file synchronization.

If you need to keep your existing Power Query connections intact, you must use Power Automate Desktop since Excel for the web does not natively support refreshing all external queries via cloud automation.

Synchronize SharePoint files

Use the OneDrive sync client to sync the target SharePoint Online folder to your local computer so the files can be accessed offline.

Verify local query connections

Open your target Excel file locally from the synced folder and ensure your Power Query successfully connects to the data source.

Create a Desktop Flow

Open Power Automate Desktop and create a new flow. Add actions to launch Excel, open the synchronized workbook, trigger the 'Refresh All' command, save, and close the file.

Use Power Automate Desktop with Local Sync

Reliable Desktop Execution: This method effectively bypasses web limitations by simulating a user manually refreshing the document within the robust desktop application.

Solution 2

Build a Scheduled Cloud Flow for Large Datasets

Ideal when queries manipulate too many rows and columns for desktop automation, utilizing direct cloud connections instead of embedded queries.

Free Microsoft Office alternative

Handle Large Datasets Efficiently with WPS Spreadsheet

If automating complex Microsoft Excel Power Queries is proving too resource-heavy or complicated for your daily tasks, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet environment to manage massive datasets without the heavy processing overhead.

1. Download WPS Office: Visit the official WPS Office website and download the free desktop application.
2. Open your datasets: Launch WPS Spreadsheet and seamlessly open your existing .xlsx or .csv files.
3. Analyze and process: Use built-in data tools, pivot tables, and formulas to process your large datasets with speed and stability.

Fully compatible with Microsoft Excel (.xlsx) formats and external data structures.Lightweight architecture ensures smoother handling of large rows and columns.Built-in advanced data processing tools with a familiar, easy-to-learn interface.Free to use without expensive subscription requirements.

microsoft office alternative - wps office

Frequently Asked Questions

Can Excel for the web automatically refresh Power Query data?

No. Excel for the web currently does not offer a universal Power Automate action that can reliably execute a 'Refresh All' command for every embedded Power Query or external workbook connection.

Why should I use Power Automate Cloud flows instead of Desktop flows?

Cloud flows run independently of your local hardware. They are the best choice when dealing with online sources like SharePoint lists directly, especially if the data volume causes local desktop queries to crash or run too slowly.

How does the OneDrive sync client help with desktop automation?

The OneDrive sync client mirrors your SharePoint cloud files to your local hard drive. This allows Power Automate Desktop to open the Excel workbook as a local file, enabling the desktop version of Excel to perform its native data refresh.

What if my Power Query processes too many rows for Power Automate Desktop?

If the query is too heavy for local processing, you should remove the embedded query. Instead, set up an Excel table and use a Power Automate Cloud Flow to directly pull and inject data from the SharePoint list into the table.

Excel & Data Problems