How to change the power BI connection string of an Excel report when I switch workspace

[Anonymous]

Hello,
I created a Power BI dataset in a test workspace. This dataset is made available to my Excel users to create pivot tables. The reports were originally created from the Analyse in Excel feature which is available on the dataset in the Power BI service. Basically, Excel reports are created automatically with the connection string on the Power BI service.

When all the Excel reports are complete and the environment seems stable to me, I intend to connect all the reports to a production workspace. I found a trick to connect them to the new workspace by generating an Excel file again with the Analyse in Excel functionality and copying the connection string on the reports already created. However, this seems to me to be bad practice.

Would there be a more user-friendly way to change the connection in the Excel report without having to change the connections for all reports? An ODBC link for example.

[Anonymous]

Hello,

Yes, there is a way to change the connection sting from Lower to higher environement to have the reports poinitng to prodution which are already created as part of test phase.

Every dataset has its own unique GUID which you can see by clicking dataset. The dataset id can be taken from the URL link.

for example

https://app.powerbi.com/....../datasets/GUID(Dataset unique ID)/......

copy only the GUID(Dataset unique ID) from the URL and replace in the excel connection string in the initial catalog( there is a GUID mapped to dev/test). Then the reprot will be automatically points to production, no need to recreate all reports again.
I hope the structures and model are in sync in lower and higher environements.

Thanks!!

[Bob_B]

I've been trying to do this as well and would appreciate the insight as well!

[Anonymous]

Hi @Anonymous ,

sorry, I don't quite understand your requirement. Are you want to move the dataset to another workspace or to change the data source of the dataset?

Best Regards,

Jay

[Anonymous]

Hi, I want to be able to easily change the Power BI dataset connection string for my various sources in Excel reports.

I have a SQL Server database called Test and another Production.

The 2 sources have the same structure but not the same data content.

I created an identical Power BI dataset called CubeX:

• One is connected to the Test database and is stored in a Test workspace.
• The other connected to the Production database is stored in a Production workspace.

The dataset is a star schema.

Basically, an Excel report was automatically generated with the Analyse in Excel feature from the CubeX dataset connected to the Test source on the Power BI service.

If we go to the Excel report, I can see it is connected to the Power BI service based on of Test database source.

So let's imagine that the user has created several dynamic tables on several tabs in the Test database connection. My goal here would be to simply change the connection string from the Excel report from test to prod.

That's what I'm doing right now. But to achieve my ends, I must recreate an Excel file from the same Analyse in Excel functionality on the CubeX dataset but in Production. I then have to open the file and copy the connection string from Production to copy it overwrite on the Excel file previously created with the Test dataset. There must be a friendlier way to do this?