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

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