Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
anuverma
New Member

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

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!

4 REPLIES 4
Omid_Motamedise
Super User
Super User

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

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
v-mdharahman
Community Support
Community Support

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

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.

lbendlin
Super User
Super User

1. Use the SharePoint Folder Connector.

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.