Use Power Query Parameters When Copying Excel Files to a New Folder
Question details
The user needs to keep Power Query, PivotTable, and chart connections working after copying related Excel workbooks to a new folder, using a folder-path parameter instead of hard-coded paths.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Copying related Excel workbooks to a new folder without breaking Power Query, PivotTable, and chart connections.
- Observed behavior
- Queries and connections can break because source file paths are hard-coded; a configurable folder path parameter should let all queries reference one source location.
Open the workbook that contains the Power Query queries and note the current source folder path. Make sure all related source and query workbooks are copied into the same new folder before updating the parameter.
Create a folder-path parameter in Power Query and reference it in each query
Use this method when you need one configurable source location for multiple Power Query workbooks.
Instead of hard-coding file paths in each query, a Text parameter stores the folder location once. When you copy the related workbooks to a new folder, updating the parameter updates all queries that reference it.
In Excel, select Data > Get Data > Launch Power Query Editor, or right-click a query in the Queries & Connections pane and select Edit.
In the Power Query Editor, go to Home > Manage Parameters > New Parameter. Name it FolderPath, set Type to Text, and set Current Value to the source folder path such as C:\OldFolder.
Select a query, open Advanced Editor, and change the source line to use the parameter, for example: Source = Excel.Workbook(File.Contents(FolderPath & "\SourceFile.xlsx"), null, true).
Click Home > Close & Load to save the query connection.
Update the folder parameter after moving the workbooks
Use this after copying the related Excel files so that you only change the source folder once.
Load Power Query results to the Data Model for PivotTables and charts
Use this approach when you want PivotTables and charts to use the Data Model instead of worksheet query results.
Use WPS Office as a free lightweight alternative for Excel workbook tasks
WPS Office is a free, lightweight Office suite that opens and edits .xlsx workbooks with a familiar ribbon interface and strong Microsoft Excel format compatibility. For native Power Query folder-parameter workflows, Microsoft Excel provides the specialized toolset; WPS Office is a practical way to view, manage, and work with copied Excel files without extra overhead.
Frequently Asked Questions
How do I create a Power Query parameter for a folder path in Excel?
Open the Power Query Editor, go to Home > Manage Parameters > New Parameter, set the type to Text, and enter the source folder path as the Current Value.
Do I need to edit every query after copying the workbooks to a new folder?
No. If each source query references the same FolderPath parameter, update the parameter once in Home > Manage Parameters and then click Data > Refresh All.
Can Power Query results feed a PivotTable without loading them to a worksheet?
Yes. In the Power Query Editor, choose Home > Close & Load To, select Only Create Connection, check Add this data to the Data Model, and then insert a PivotTable from the Data Model.
Does splitting data into separate workbooks improve Power Query performance?
Usually not. Retrieving data from multiple separate workbooks adds connection overhead and can be slower than keeping related tables in the same workbook.
What should I do if source file names change after copying?
Update any query references that use the changed file name, or use a separate parameter for the file name so you can change it in one place.
