Power Query Problems

How to Automatically Update Duplicate Excel Files in SharePoint and OneDrive

Aamir Naveed Akram Oct 23, 2026 870 views

Question details

The user wants to automate the updating of duplicate Excel workbooks stored in SharePoint and OneDrive using Power Query to avoid repeated manual data entry and outdated file copies.

How to Automatically Update Duplicate Files in SharePoint and OneDrive via Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing large duplicate Excel files across cloud storage (SharePoint/OneDrive) and keeping them synced without manual intervention.
Observed behavior: Currently experiencing outdated copies of large files and high manual workload to update duplicate Excel files.

Before you start

Ensure you have the exact file paths or Web URLs for your SharePoint and OneDrive workbooks, and verify that your account has the necessary access permissions to connect to these cloud directories.

Solution 1Recommended

Create a Centralized Workbook with Multiple Power Queries

This method keeps all queries in a single file for cleaner data management and easier merging of data models.

Keeping all your data connections in one centralized Excel file can simplify file management. You can create multiple queries pointing to your SharePoint or OneDrive files and then combine or merge the queries into a comprehensive data model within one workbook.

Launch a new workbook

Open a new Microsoft Excel workbook and navigate to the 'Data' tab on the main ribbon.

Connect to SharePoint or OneDrive

Click 'Get Data', select 'From File' or 'From Online Services', and choose 'From SharePoint Folder' or input your OneDrive file path.

Load your files

Load the multiple duplicate files into Power Query by repeating the connection process for each necessary data source.

Merge the queries

Use the 'Append Queries' or 'Merge Queries' buttons within the Power Query Editor to consolidate the incoming data into your centralized sheets.

Create a Centralized Workbook with Multiple Power Queries

Performance Note: Having a large number of queries (e.g., 20 or more) in a single workbook can increase the refresh time. It is recommended to filter your data as early as possible in the query processing stage.

Solution 2

Use Separate Query Files Connected to a Single Source

This approach is best when you want to distribute the processing load across multiple files to avoid creating a single, sluggish workbook.

Free Microsoft Office alternative

Discover WPS Office for Lightweight and Efficient Data Management

While complex Power Query setups in Microsoft Excel can lead to slow refresh times and heavy files, WPS Office offers a lightning-fast, highly compatible alternative for everyday spreadsheet tasks. It perfectly supports Microsoft Excel formats and provides a clean, intuitive interface for seamless data analysis without the bulk.

1. Download the software: Visit the official WPS website to download and install WPS Office for free.
2. Open WPS Spreadsheet: Launch the application and select the 'Spreadsheet' module from the home screen.
3. Edit your Excel files: Open your existing .xlsx files directly in WPS Office to enjoy fast and compatible data editing.

100% free and fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Lightweight installation with incredibly fast loading times for large datasets.Built-in cloud syncing functionality to easily manage and backup files across devices.Familiar, tabbed user interface ensuring zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Power Query refresh so slow when connecting to SharePoint?

Slow refresh times often occur when connecting to large files over a network or when processing many queries simultaneously. To improve speed, filter your data within the Power Query Editor before loading it into Excel, and remove any unnecessary columns or rows to minimize the data payload.

Can I connect Power Query to a OneDrive personal account?

Yes, you can connect to files in OneDrive. However, you must either use the local file path generated by the OneDrive sync client on your PC, or use the Web URL of the file via the 'From Web' data source option (often requiring you to change the end of the URL from '?web=1' to '?download=1').

Is it better to have many queries in one file or multiple files with fewer queries?

It depends on your workflow. A single file centralizes your data and makes merging easier, but can become slow to refresh if overloaded. Multiple files distribute the processing load but can be harder to manage and track across a team.

Will duplicate files update automatically in real-time when the SharePoint source changes?

No, they will not update instantly in real-time. You need to either manually click 'Refresh All' on the Data tab, or configure the workbook's Connection Properties to refresh the data automatically when opening the file or at specific time intervals (e.g., every 30 minutes).

Excel & Data Problems