Changing the Power query SQL Server database settings when moving an Excel workbook

0
votes

I'm working on creating an excel report that collects data from a local copy of a SQL Server database on my maschine, where I use Power Query to retrieve the data. These are then loaded into a PowerPivot data model. Now I'm finished with the development and on my way to put this into production on another server on the customer's server. The Excel workbook must change database settings using sql server database user Connection (not integrated). I had hoped that I could change the database Connection Properties at the Data tab, but there are not any easy way to change the connection string to the new server. Now I can't see any option that going through every Power Query query and change them manually. I have great hope that you Power Query experts have a Nice explaination and an example how I can solve this.

Hope to hear from you soon

Regards Geir F

2
excelpowerpivotdaxpowerqueryvba
There are 2 type of security settings: file-wide and local machine-wide. Do as Alejandro says, and select "Data Sources in Current Workbook". This will change saved settings. AFAIR this doesn't require datasource to be the 1st line of the query, though. – Eugene

2 Answers

0
votes

There isn't a great solution for bulk server rename today, but we're very aware of the customer demand! (I can't promise anything about upcoming features, but at some point in the past I heard the dev team discuss this feature.) I'd recommend showing your support for this feature at https://excel.uservoice.com/

If you need to solve this soon, manually opening each query and editting the server string is what you need to do, sorry :\

(If you're building new reports again, Power BI Desktop lets you parameterize the server name to a top-level query, which would allow for quick rename operations!)

0
votes

Do you only need to change the server name? If you go to the Data Source Settings window, you can select the SQL Server source you are using and click on the "Change Source..." button. If you change the server name in that dialog, it will change the server name in all of the queries that use that source (assuming it's the first step in the query).