Change data source settings for all queries in direct query mode

[cid255]

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?

[Kelhammer]

https://community.powerbi.com/t5/Community-Blog/Why-can-t-I-change-the-Data-Source-in-Power-BI-disab...

[v-yulgu-msft]

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.

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

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

[Anonymous]

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?

[cid255]

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

I have two queries which use the same database.

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.

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.

🙂

[v-yulgu-msft]

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

[Anonymous]

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