How to Update Power Query File Paths in Excel
Question details
The user needs to update the file paths in Excel Power Query connections because the source files were relocated from a personal drive to a shared folder.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Moving data source files to a new directory or shared folder and needing to redirect existing queries to the new location.
- Observed behavior
- The Power Query connections use the old, invalid paths, causing the Excel data model to break and fail to refresh.
Ensure you have the exact file path of the new shared folder and verify that you have read access to the moved source files before opening the Power Query Editor.
Create a Path Parameter for Easier Future Maintenance
Set up a parameter for your folder path so you only need to update it in one central place if files are moved again.
Hardcoding file paths in every query makes maintenance difficult if you frequently move files. Using a parameter allows you to define a folder path once and reference it across multiple queries. When the folder changes, updating the single parameter fixes all dependent connections.
Open your Excel workbook, navigate to the 'Data' tab on the ribbon, and select 'Get Data' > 'Launch Power Query Editor'.
In the 'Home' tab of the Power Query Editor, click 'Manage Parameters' and select 'New Parameter'. Name it something identifiable like 'FolderPath'.
Set the 'Type' to 'Text', paste the new shared folder path into the 'Current Value' box, and click 'OK'.
Select a query from the left pane. Under 'Applied Steps' on the right, click the gear icon next to 'Source'. If the dialog allows, change the file path input to use the 'Parameter' and select 'FolderPath'. Alternatively, open the 'Advanced Editor' and modify the code to concatenate the parameter with the file name (e.g., File.Contents(FolderPath & "\filename.xlsx")).
Click 'Close & Load' to apply the changes to your Excel data model and refresh the data.
Manually Update the Source Step
Update the file path directly in the applied steps of each query if you only have a few connections to fix.
Try WPS Office for Seamless Data Handling
If you need a lightweight, fast, and highly compatible spreadsheet tool, WPS Office is an excellent free alternative. It provides a familiar interface and robust data management features without heavy resource usage.
- 1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
- 2. Open WPS Spreadsheet: Launch the WPS Spreadsheet application from your desktop or start menu.
- 3. Access Your Data: Click 'Open' and select your existing .xlsx workbook to continue managing your data and calculations seamlessly.
Frequently Asked Questions
Why did my Power Query connections break after moving files?
Power Query connections rely on absolute file paths by default. When source files are relocated to a new personal drive or a shared network folder, the original paths stored in the query's Source step become invalid, resulting in data refresh errors.
Can I update multiple Power Query paths at once?
Yes, but only if you have configured your queries to use a parameter for the folder path. Once linked, changing the parameter's Current Value will simultaneously update the source path for all queries referencing that parameter.
What should I do if only the file name changes?
If the file remains in the same folder but the name changes, you must manually edit the Source step of the specific query to point to the new file name. Parameters can also be used for file names if they change on a regular basis.
Do I need to refresh the Excel data model after updating the path?
Yes. Updating the path in Power Query fixes the connection, but you must click 'Close & Load' and then manually 'Refresh All' in Excel to pull the most recent data from the new file location into your workbook.
