How do I change the Power Query data source for multiple excel files at once?

[anuverma]

I have multiple excel files that have a connection to a master file stored on my local drive. I am planing to move this master file to a cloud folder. How do I change the data source for all the files at once?

Please note that my question is not about changing the data source for multiple queries in a single excel file but rather changing the data source in multiple files without having to individually open each file and then update the data source.

Thanks in advance!

[Poojara_D12]

Hi @anuverma 

You're looking to update the data source connection for multiple Excel files that currently point to a master file stored on your local drive, which you plan to move to a cloud location (like OneDrive, SharePoint, or a network URL). The challenge here is that you're not asking how to update multiple queries within one Excel file, but rather how to bulk update the data source across many separate Excel workbooks without opening each file individually—a common need in enterprise environments.

Unfortunately, Excel and Power Query do not offer a built-in way to batch-edit data source paths across multiple files. However, this can be addressed using automation via scripting, typically with PowerShell, VBA, or a combination of tools like Office Scripts (for cloud-based files). The idea is to programmatically open each Excel file, locate the Power Query (M) expressions that contain the source path (e.g., Excel.Workbook(File.Contents("C:\LocalPath\..."))), and replace the old path with the new cloud-based path (e.g., https://.../Shared Documents/...).

In PowerShell, for example, you can use the Excel COM object to loop through files in a folder, access the Power Query objects (Workbook.Queries), and update the Formula property for each one. This lets you replace the hardcoded path in the query definition across all files. Similarly, VBA macros can be written to do this if the workbooks are opened in Excel Desktop, though it requires opening each file briefly in the background.

If your files will be moved to SharePoint or OneDrive, another long-term solution is to parameterize the source path in Power Query using relative paths or named parameters, which can make future updates easier by centralizing the file location reference.

In summary, there's no native Excel feature to bulk-update data sources across multiple files, but with a bit of scripting (PowerShell or VBA), you can automate this process by editing the M code behind each file’s query, allowing you to redirect all your Excel files to the new master file location without manually opening them one by one.

[Omid_Motamedise]

Hi @anuverma 

If all files have similar M code structure and refer to the same local path, you can programmatically update the source path by doing a find-and-replace inside each Excel file’s .xlsx or .xlsm content by following steps.

Excel files are actually ZIP archives. The Power Query M code is stored in the customXml folder inside them. So:

Rename .xlsx → .zip

Extract it

Open files under customXml and locate the M code (usually in item1.xml)

Do a find/replace of the old path with the new cloud path

Re-zip and rename back to .xlsx

[v-mdharahman]

Hi @anuverma,

Thanks for reaching out to the Microsoft fabric community forum.

Based on your description, you're dealing with multiple Excel files that each connect to a master file via Power Query or external links, and you're now moving that master file to a cloud location. You want to update the path in all the connected files without manually opening each one which is a very valid requirement, especially at scale.

You can go through the response provided by @lbendlin and check if your issue can be resolved or you can follow this step where you can automate the update of the data source using PowerShell. This script will open each file silently, update the connection string or Power Query M code, save, and close it:

$folderPath = "C:\Your\Folder\With\ExcelFiles"
$oldPath = "C:\Path\To\Old\Master.xlsx"
$newPath = "https://yourcloudlocation.com/Path/To/New/Master.xlsx"

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

Get-ChildItem -Path $folderPath -Filter *.xlsx | ForEach-Object {
    $workbook = $excel.Workbooks.Open($_.FullName)

    foreach ($connection in $workbook.Connections) {
        if ($connection.Type -eq 6) { # 6 = xlConnectionTypeWORKBOOK
            $connString = $connection.OLEDBConnection.Connection
            if ($connString -like "*$oldPath*") {
                $connection.OLEDBConnection.Connection = $connString -replace [regex]::Escape($oldPath), $newPath
            }
        }
    }

    # For Power Query / M code changes (if applicable):
    foreach ($query in $workbook.Queries) {
        $formula = $query.Formula
        if ($formula -like "*$oldPath*") {
            $query.Formula = $formula -replace [regex]::Escape($oldPath), $newPath
        }
    }

    $workbook.Save()
    $workbook.Close()
}

$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

Make sure to replace the $folderPath, $oldPath, and $newPath with your actual paths. This script assumes the connection string or Power Query M code contains the old file path, and replaces it with the new one. Also be sure to back up your Excel files before running the script, as it will overwrite them.

I would also take a moment to thank @lbendlin, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

[v-mdharahman]

Hi @anuverma,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If so, it would be really helpful for the community if you could mark the answer that helped you the most. If you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[v-mdharahman]

Hi @anuverma,
I just wanted to follow up on your thread. If the issue is resolved, it would be great if you could mark the helpful reply as solution so other community members facing similar issues can benefit too.
If not, don’t hesitate to reach out, we’re happy to keep working with you on this. 

Best Regards,

Hammad.

[v-mdharahman]

Hi @anuverma,
We noticed there hasn’t been any recent activity on this thread. If your issue is resolved, marking the correct reply as a solution would be a big help to other community members. If you still need support, just reply here and we’ll pick it up from where we left off.

Best Regards,

Hammad.

[lbendlin]

1. Use the SharePoint Folder Connector.

2. Use Parameters to avoid hard coding the source details for each query