Power Query Problems

Share Power Query Data Through SharePoint and Excel

Muhammad TalhaMuhammad Talha Nov 18, 2026 869 views

Question details

Share transformed Power Query results from a central workbook with individual Excel users for Power Pivot analysis.

Share Power Query Data Through SharePoint and Excel
Product
Microsoft Excel Power Query, Power Pivot, SharePoint, and Microsoft Access
Device & OS
not provided
Scenario
A central workbook combines Access and Excel data, and users need to connect separate workbooks to two final queries stored in SharePoint.
Observed behavior
The individual workbooks do not show the source workbook's queries when users connect to it as an Excel file.
Before you start

Confirm that the central workbook refreshes successfully and that users have access to the SharePoint library. Check whether the two final queries are loaded to worksheet tables; queries that exist only as connections or in the Data Model are not exposed as worksheet tables to the Excel workbook connector.

Solution 1Recommended

Share the final query results as Excel tables

Use worksheet tables in the central workbook when individual users need to import the transformed results into their own Data Models.

Connecting to an Excel workbook imports exposed workbook contents, such as tables and sheets; it does not automatically give users the source workbook's Power Query definitions. Load each final query to a worksheet table so it can be selected in Navigator.

1
Load each final query

Open the central workbook in desktop Excel. Under Data > Queries & Connections, right-click each final query, select Load To, and choose Table on a worksheet.

2
Save and share the workbook

Save the central workbook in a SharePoint document library. Use the library's Share command to give the individual users access.

3
Connect an individual workbook

Open an individual workbook in desktop Excel. Select Data > Get Data > From File > From Workbook, then choose the central workbook from an accessible SharePoint-synced folder.

4
Import the result tables

In Navigator, select the tables containing the final query results. Choose Load To and select Add this data to the Data Model if the user needs it for Power Pivot analysis.

5
Refresh in order

Refresh and save the central workbook first using Data > Refresh All. Then open each individual workbook and select Data > Refresh All to retrieve the updated tables.

Share the final query results as Excel tables
Check paths and permissions: A synced-folder path can differ between users. Each user must be able to reach the workbook at the path used by their connection; if users also refresh the central workbook, they need access and suitable credentials for its underlying sources.
Free Microsoft Office alternative

Work with shared Excel tables in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks to review and edit shared tables while moving between Office tools. Microsoft Power Query connections and Power Pivot Data Models may not work the same way in WPS.

Review shared query-result tables in a familiar spreadsheet interfaceCompatible with Microsoft Excel formats, including XLSXFree and lightweight for everyday spreadsheet workOpen existing Office files to support a smoother transition
microsoft office alternative - wps office

Frequently Asked Questions

Why can't I see the source workbook's queries in Navigator?

The Excel workbook connector shows accessible workbook contents, such as tables and sheets, rather than automatically exposing Power Query definitions. In the source workbook, use Data > Queries & Connections and Load To to load each final query to a worksheet table.

Can users load the shared results into Power Pivot?

Yes. In desktop Excel, import the result tables and choose Load To > Add this data to the Data Model. Users can then use the Data Model for Power Pivot analysis if their Excel version supports it.

Do all users need access to the original Access database?

Users who only import saved tables from the central workbook generally need access to that workbook, not its original sources. Anyone refreshing the central workbook's Access connection needs access to the database and any required credentials.

Why aren't updated source records appearing in an individual workbook?

Refresh the central workbook with Data > Refresh All and save it to SharePoint first. Then use Data > Refresh All in the individual workbook and check that its connection points to the intended file.