Data Import & Export

Connect Excel Tables in Separate SharePoint Workbooks with Power Query

John WilsonJohn Wilson Nov 12, 2026 868 views

Question details

Create a stable connection between tables stored in separate SharePoint Excel workbooks without relying on external VLOOKUP formulas.

Connect Excel Tables in Separate SharePoint Workbooks with Power Query
Product
Microsoft Excel and SharePoint
Device & OS
not provided
Scenario
The user needs to import, combine, or relate data from a source workbook in SharePoint to a destination workbook.
Observed behavior
VLOOKUP results work while both workbooks are open but do not update reliably after the SharePoint source workbook is closed.
Before you start

Confirm that the source data is formatted as an Excel table and that you have permission to open the SharePoint site and files. Copy the SharePoint site URL only, rather than a link to an individual workbook, for the SharePoint Folder connector.

Solution 1Recommended

Import SharePoint workbook tables with Power Query

Use this recommended method when you need refreshable data from closed SharePoint workbooks and want to merge or append tables without external worksheet formulas.

Power Query reads data from the SharePoint files during a refresh, so the source workbook does not need to remain open. You can load each table as a query, then merge matching records or append rows from multiple tables.

1
Connect to the SharePoint folder

In Excel, select Data > Get Data > From File > From SharePoint Folder. Enter the SharePoint site URL, sign in with your work or school account, and select Connect.

2
Filter to the source workbook

In the file list, filter the Name column to locate the required .xlsx workbook. Select the Content value for that file, then choose Transform Data to open Power Query Editor.

3
Select the Excel table

In Power Query Editor, select the workbook content table, filter the Kind column to Table, and choose the required table name. Select the data value to expand the table columns.

4
Merge or append the imported queries

Load the other SharePoint table as another query. In Power Query Editor, select Home > Merge Queries to match records by a common key, or select Home > Append Queries to combine rows with the same column structure.

5
Load and refresh the result

Select Home > Close & Load to place the results in the destination workbook. To update later, select Data > Refresh All.

Import SharePoint workbook tables with Power Query
Closed source workbooks are supported: Power Query refreshes the imported SharePoint data without requiring the source Excel workbook to be open.
Free Microsoft Office alternative

Work with Connected Excel Tables in WPS Office

If you need a free, lightweight office suite for opening and editing workbook data, WPS Office provides a familiar spreadsheet interface and strong Microsoft Office format compatibility. You can migrate existing XLSX files seamlessly and continue organizing, reviewing, and calculating table-based data in WPS Spreadsheets.

  1. 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the home screen.
  2. 2. Open the Excel workbook: Select Open and choose the existing .xlsx workbook from your computer or synced SharePoint folder.
  3. 3. Review and edit table data: Select a table cell to use spreadsheet tools for formulas, sorting, filtering, and data updates.
Open and edit Microsoft Excel XLSX and XLS files with high format compatibility.Use familiar spreadsheet tables, formulas, filters, and PivotTable-style analysis tools.Lightweight, free office suite for everyday workbook editing and data review.Seamlessly migrate existing Excel workbooks without converting them to another file format.
microsoft office alternative - wps office

Frequently Asked Questions

Why does a VLOOKUP link stop updating when the SharePoint source workbook is closed?

External worksheet formulas can have limitations with closed cloud-hosted workbooks. Power Query avoids this dependency by importing the source table during Data > Refresh All.

Which SharePoint URL should I enter in Excel Power Query?

Enter the site-level URL, such as https://company.sharepoint.com/sites/SiteName, in Data > Get Data > From File > From SharePoint Folder. Do not paste a direct workbook sharing link.

Can Power Query merge tables from two different SharePoint Excel files?

Yes. Create a query for each workbook table, then select Home > Merge Queries in Power Query Editor and choose the matching key columns, such as an ID field.

When should I append queries instead of merging them?

Append queries when the tables have the same or similar columns and you want to stack their rows. Use Home > Append Queries. Merge queries when you need to match records using a shared field.

Do I need to open the source SharePoint workbook before refreshing Power Query?

No. Once the query credentials and source path are configured, select Data > Refresh All in the destination workbook to retrieve the latest available data from SharePoint.