cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cid255
New Member

Change data source settings for all queries in direct query mode

Hi,

 

I want to use the data source settings to change the server and dbname for all my queries on will.

As soon as I have a second query in my workbook thougg, that option is greyed out, even if the same server/db values are used within.

 

Is there a way to achieve this without using parameters?

Can I somehow access the values given as server and database from within M? Are there global/system variables or functions to use for this?

 

6 REPLIES 6
v-yulgu-msft
Microsoft
Microsoft

Hi @cid255,

 

To change the data source connection string, you can locate to File -> Options and Settings -> Data source settings. Change the server name and database name to what you want.

1.PNG

 

Alternatively, you can change server name and database name via M query in Query Editor mode.

2.PNG

 

By the way, what did you mean "have a second query in my workbook thougg, that option is greyed out"? What was the workbook? And which option is greyed out? 

 

Do you want to change the data source to workbook which should be connected to in import mode? Since you have used live connection in current pbix file, it is not possible to connect/change to another data source with import mode.

 

There were multiple queries in your pbix file, you want to change data source settings for all of them at once, right? However, per my knowledge, it is not available to achieve that. 

 

Regards,
Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I am facing same problem. My change source option also grayed out. Can you please help me on this.

 

Is there any limitations to use change datasource option?

No, I don't want to swtich to import, direct query is fine.

 

I have two queries which use the same database.

 

PBI Queries.png

PBI Query1.pngPBI Query2.png

 

And once I add the second one, I can no longer change the source using the option you pointed to.

The refresh though works as intended.

 

PBI Data Source.png

 

While the report moves from DEV to PROD, the data source needs to be changed, and since the report is supposed to work with different databases which have a common structure, even the enduser should be able to change the data source.

 

The the data source settings would be the best fit to do so. I definitely don't want anyone to mess with the M Script, and using the parameters to achieve that also seems undesired, as those are already used for their original purpose of adapting the data acquired/the calculations done.

 

Since direct query restricts us to use only 1 datasource, it would make sense to change all the queries at once. Either by Power BI changing the M code on its own, or by being able to point to the strings entered in the data source settings from within the M.

So if none of this is possible I have my answer, although it is not what I hoped for.

 

🙂

 

Hi @cid255,

 

However, I didn't encounter the problem as you described. I loaded two queries into desktop from the same data source/database, I was still able to change the data source settings. The option was not greyed out.

 

 In your scenario, does this problem occur to other pbix files?

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi are you able to resolve this. I have one of my report in which my change source option has been grayed out. It works fine in other reports for same datasources.

 

Is there any limitations to use this change source option in datasource settings?

 

Regards,

Pradeep Reddy

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors