'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Can Power Query Read Data from a Live SharePoint Workbook?
Question details
The user wants to know if Power Query can extract data from a live Excel workbook hosted on SharePoint, specifically regarding unsaved changes in an active session.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Connecting Power Query to an active Excel workbook hosted on SharePoint to pull real-time data.
- Observed behavior
- Power Query retrieves data from the last saved version of the SharePoint workbook, ignoring any unsaved changes currently held in memory by active users.
Ensure that you have appropriate read permissions for the SharePoint folder or file you are trying to connect to, and verify that the file path URL is correct.
Connecting to a SharePoint Workbook and Fetching Updates
Set up the connection properly and understand the requirement of saving the source file to retrieve new data.
Power Query reads data from the saved file on the SharePoint server, not from the local memory of an open Excel session. Therefore, any real-time edits made by you or another user will not be visible to Power Query until those changes are explicitly saved to SharePoint. Keeping your query in a separate destination workbook is highly recommended to prevent file lock issues or interference with the live source file.
Open the source workbook in the Excel desktop app, go to File > Info, click 'Copy Path', and remove the '?web=1' suffix from the copied URL.
In your separate destination workbook, go to Data > Get Data > From File > From Workbook (or From Web), and paste the SharePoint URL.
Before attempting to refresh your data in the destination workbook, ensure the source workbook on SharePoint has been saved so that all memory-only edits are committed to the disk.
In the destination workbook, navigate to Data > Refresh All to pull the newly saved changes from the SharePoint source file.
Manage Your Spreadsheets Seamlessly with WPS Office
While Power Query and SharePoint live connections are specific to the Microsoft ecosystem, WPS Office provides a lightweight, highly compatible alternative for everyday data analysis and spreadsheet management. It's free, fast, and fully supports standard Excel formats.
- 1. Download and Install: Get WPS Office Free from the official website and install it on your device.
- 2. Open Your Excel File: Launch WPS Spreadsheets and click 'Open' to load your existing .xlsx workbooks.
- 3. Analyze Data: Use WPS Spreadsheets' built-in tools like PivotTables, formulas, and charts to manage your data.
Frequently Asked Questions
Why does Power Query not show the latest data from a shared workbook?
Power Query reads the file saved on the server (or disk). If another user has the file open and is making changes, those changes exist only in their local memory until they save the document. Once saved, a Power Query refresh will fetch the updated data.
Can I connect Power Query to a SharePoint file that is currently open?
Yes, Power Query can read an open file stored on SharePoint. However, it will only retrieve the data as it was last saved to the server, ignoring any unsaved, real-time edits.
How do I avoid file lock errors when refreshing Power Query from SharePoint?
Keeping your queries in a separate destination workbook rather than the source workbook reduces interference and prevents file locking issues when multiple users are accessing the live SharePoint file.
Does AutoSave help Power Query get live updates faster?
Yes, if AutoSave is enabled on the SharePoint workbook, it frequently commits changes to the cloud. This makes the newly added data available to Power Query much quicker than waiting for a manual save.
