How to Share Power Query Workbooks via OneDrive or SharePoint
Question details
The user needs to share an Excel workbook containing Power Query connections so that other team members can access and refresh the queries.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Collaborating on data-driven Excel files where team members need to run and view queries pulled from external folders or files.
- Observed behavior
- Power Query privacy settings do not automatically share queries across users, meaning users need explicit access to both the workbook and the data source connections to avoid errors.
Ensure that all team members who need to refresh or edit the queries have the necessary permissions to access both the shared workbook and the underlying data source locations.
Store and Share the Workbook via SharePoint or Teams
Centralizing the workbook and source files in SharePoint or Microsoft Teams is the most reliable approach for team collaboration and consistent query access.
Because queries are stored inside the Excel workbook, moving both the workbook and its data sources to a shared organizational location prevents 'Data source not found' errors when colleagues try to refresh the data.
Navigate to your shared SharePoint Document Library or a Microsoft Teams channel 'Files' tab. Upload the Excel workbook and all related source files (e.g., CSVs or other Excel sheets) to this centralized folder.
Open the workbook in Excel Desktop. Open Power Query Editor, go to 'Data Source Settings', and update the source paths to use the SharePoint folder or Web paths instead of your local C:\ drive.
Click the 'Share' or 'Manage Access' button in the top right corner of Excel or SharePoint. Grant 'Read' or 'Edit' permissions to your team members so they can open the workbook.
Share a Direct Link via OneDrive
Use OneDrive sharing for simpler, one-on-one scenarios where the recipient already has access to the required source data.
Experience Seamless Spreadsheet Collaboration with WPS Office
If you are looking for an intuitive, lightweight, and highly compatible suite for your spreadsheet and data analysis needs without the complex sharing configurations of Microsoft Office, WPS Office is an excellent choice.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open Your Spreadsheets: Open your existing .xlsx workbooks seamlessly without worrying about format distortion.
- 3. Collaborate via Cloud: Save your files to WPS Cloud and share a collaboration link directly with your team.
Frequently Asked Questions
Why do my team members get a 'Data source not found' error when refreshing a shared query?
This happens if the query connects to a local file path (like C:\Users\...) on your computer instead of a shared cloud location. Update the Data Source Settings in Power Query to use the SharePoint or web URL path for the source files.
Can I share just the Power Query without sharing the whole workbook?
No, queries are stored directly inside the Excel workbook itself. To share a query, you must share the workbook that contains it, or export the connection as an Office Data Connection (.odc) file for others to import.
Does sharing a OneDrive folder automatically share the Power Queries inside it?
Sharing a folder gives access to the files within it, but it does not consolidate the queries. Users must still open the specific workbook containing the queries to view or run them.
What are Power Query privacy levels and how do they affect sharing?
Privacy levels (Public, Organizational, Private) determine how data can be combined across different sources to prevent data leakage. These settings are specific to each user and do not automatically sync, meaning each user may be prompted to set their own privacy levels upon their first data refresh.
