'%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)
How to Fix Power Query Prevents Saving Excel Workbook While Source is Open
Question details
The user is unable to save an Excel workbook because Power Query is actively connected to another source workbook that is currently open, resulting in a file-locking error.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Trying to save a destination workbook containing a Power Query connection while the source workbook is simultaneously open.
- Observed behavior
- Excel throws a file-locking error and prevents the user from saving changes to the destination workbook until the source file is closed.
Ensure that you have saved any unsaved manual changes in both workbooks to a temporary file or copied them to your clipboard to prevent data loss before closing the locked files.
Close the Source Workbook Before Saving
The most direct way to resolve the Power Query file-locking issue is to close the source file being queried.
Power Query places a lock on the source workbook during data retrieval to prevent structural changes or data corruption. Closing the source file releases this lock immediately.
Navigate to the source workbook (often referred to as File B) that your Power Query is pulling data from.
Click 'File' and then 'Save' to ensure any manual changes you have made in this source file are safely stored.
Close the source workbook completely by clicking the 'X' in the top right corner of the window.
Return to your destination workbook (File A) that contains the Power Query and click 'Save' or press Ctrl+S.
Use Alternative Data Lookup Formulas
If you need both files open simultaneously for active editing, consider using formula-based lookups instead of Power Query.
Open the Source Workbook in Read-Only Mode
Opening the source file as read-only allows Power Query to read it while letting you keep the file open for reference.
Use WPS Office for Seamless Data Management
If you frequently encounter file-locking issues or performance lags with complex Excel queries, try WPS Office. It is a free, lightweight, and highly compatible alternative that handles multiple workbooks smoothly without heavy background processes.
- 1. Download the installer: Visit the official WPS Office website and click the free download button.
- 2. Install WPS Office: Run the downloaded installer and follow the quick on-screen instructions to set up the software.
- 3. Open your workbooks: Launch WPS Spreadsheet and open your existing .xlsx files directly with full format compatibility.
Frequently Asked Questions
Why does Power Query lock my Excel file?
Power Query establishes a dedicated data connection that actively reads the source file. To prevent data corruption or structural shifts during this read process, Excel places a lock on the file, which prevents simultaneous saves while both files are communicating.
Can I refresh a Power Query connection while the source file is open?
Yes, you can often refresh the query while the source file is open. However, attempting to save the destination file or making structural changes to the source file during an active connection is what usually triggers the file-lock error.
How do I forcibly remove a file lock in Excel?
The safest and easiest way to remove a file lock caused by Power Query is to close the source workbook. If the lock persists, you may need to cancel any active background query refreshes from the Data tab or restart Excel entirely to clear stuck background processes.
Will changing file permissions solve the Power Query lock issue?
Changing the source file to 'Read-Only' can bypass the lock. Because the system knows you won't be writing new data to the source file, it allows the destination file's query to execute and save without raising a conflict.
