Updated Server and database Name dynamically

[Nicki]

Hello,

Is there any way to update server name and database name in power BI desktop by reading from a file, query or API?

After that data will  be updated in Power BI desktop automatically.

Thanks

Nicki

[cnschulz]

This does NOT work on Power BI Report Server. This is amazingly frustrating.

[watkinnc]

You can reference an external Excel file for that.  Let's say you have a SQL server and database source like:

Sql.Databases("DESKTOP-738NO1A\SQLEXPRESS")

If you have an excel file with a table with two columns: DataSource, Value

DataSource can be AdventureWorks, Value can be "DESKTOP-738NO1A\SQLEXPRESS".

Use Excel.Workbook to query the excel file, choose that table, right click the "cell' in which your "DESKTOP-738NO1A\SQLEXPRESS" resides. Nae that query something, like SQLSource.

Now, you can go back and change the source in  AdventureWorks to SQL.Databases(SQLSource)

So now whenever you update that excel sheet, you are hanging the source as well--once you refresh they query.

---Nate

[edhans · Accepted solution]

You can use a query to pull that name in. Make it a scalar value - just right-click on the final result and Drill Down. That will show something as a text value. Rename the query to something like varServerName, and be sure to right-click on that and uncheck "Enable Load"

Then, in the actual query where you need this replace the server name with varServerName.

So:
= Sql.Database("Some_Server_Name", "DATA")

becomes
= Sql.Database(varServerName, "DATA")

Do the same with the database name.

You can see a bit more about this in my Dynamic Date Table post. I use the same logic to get a start and end date for a date table. Same concept, except you'll be getting a text value for a server name instead of a date value.

Creating a Dynamic Date Table in Power Query