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!

[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

[lbendlin]

1. Use the SharePoint Folder Connector.

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