Fix Excel Power Query Refresh Requiring the Source Workbook
Question details
The user needs an Excel query table to refresh without manually opening its source workbook.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Refreshing a query table that reads data from a table in another workbook.
- Observed behavior
- The affected query may fail unless the source workbook is open, while other workbook queries refresh correctly.
Locate the source workbook and identify the table the failing query should read. Confirm that the file is accessible from the same account and location used to refresh the query.
Compare and correct the query source settings
Use a working query as a reference to find differences in the failing query's path, connection, or table reference.
In Excel, select Data > Queries & Connections, then right-click the failing query and choose Edit.
In Power Query Editor, select Source and Navigation under Applied Steps. Check the workbook path, connection type, and selected table name against the intended source.
Open a query that refreshes successfully and compare its Source and Navigation steps with those of the failing query. Look for differences beyond the file name, including how the workbook and table are referenced.
Use the settings icon next to an applicable step, or select Home > Advanced Editor, to correct the path or table reference. For a separate workbook, ensure the query references the accessible file rather than relying on data available only in the current workbook.
Select Home > Close & Load, close the source workbook, and then select Data > Refresh All in the destination workbook.

Check access and credentials for the source workbook
Use this when the source path looks correct but the query cannot access the file independently.
Recreate the affected workbook connection
Use this if the existing query has an incorrect or damaged connection that cannot be repaired easily.
Work with Excel Workbooks in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open and edit compatible Excel files for an easier move between office suites; repairing Microsoft Excel Power Query connections still requires Excel.

Frequently Asked Questions
Does the source workbook have to be open for Power Query to refresh?
Usually, a query that connects directly to an accessible workbook file can refresh while that workbook is closed. Check the Source step in Power Query Editor to see how the file is referenced.
Where can I see which table the query uses?
Select Data > Queries & Connections, right-click the query, and choose Edit. In Power Query Editor, inspect the Navigation step under Applied Steps for the selected table or sheet.
What if the source workbook was moved or renamed?
Open the query in Power Query Editor and update its Source step to point to the new file location. Then select Close & Load and test the refresh.
How do I check the credentials used to access the source?
In Excel, select Data > Get Data > Data Source Settings, select the source, and review its permissions or edit the stored credentials.




