Share Power Query Data Through SharePoint and Excel
Question details
Share transformed Power Query results from a central workbook with individual Excel users for Power Pivot analysis.

- 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.
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.
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.
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.
Save the central workbook in a SharePoint document library. Use the library's Share command to give the individual users access.
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.
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.
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.

Connect individual workbooks through the SharePoint folder connector
Use the SharePoint folder connector when users need to retrieve the central workbook from the library rather than a local sync path.
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.

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.




