Excel

A while ago I’ve received an email from Jason Alvarez asking me if I knew a way to change Power Query connections inside an Excel file. The problem is similar to my previous post on Mass changing pivot table connection strings in Excel using PowerShell. Turns out you can and he was able to find this solution: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 #root path to the collection of excel docs: $rootPath = "C:\Temp\PowerQueryDemo\" #Current query file path: $currentPath = "C:\Temp\PowerQueryDemo\" #Updated query file path: $newPath = "C:\Temp\PowerQueryDemo\PowerQueryNewSource" Get-ChildItem -Path $rootPath -Recurse -Include *.

The Problem As Business Intelligence consultants after a migration to a brand new server inevitably we get asked by the users: “Do why have to redo all my pivot tables in Excel again? All my workbooks stopped working…” Well, they can but they don’t need to. Where’s a simple code to replace the part that matters. This will iterate all the Excel files in the folder where the Powershell Script is located and and apply the substitution to all the OLEDBConnections that exist in each file: