can I change the data source from test to production database in PowerBI desktop?

[jliu]

I defined the power queris using the test database in powerBI desktop. I'd like change the data source to point to the production database. I used the way from 'File->Options and Settings -> Data Source Settings'. But I can only change the Credentials without being able to change it to a different database. Are there anyways we change the data source definition just like the one we used in Powerpivot inside Excel?

[BastiaanBos]

Dear PowerBi peeps,

I'm also having issues with changing the datasource. 
When I change the datasource from my test enviroment to the production.
powerbi desktop wants to apply the change before I can publish or save the PBIX file. Which offcourse sounds logical. But...

My test eviroment has
200MB of data containing a few thousend lines
My production eviroment has
3,5 Gigs of data containing a few million lines.

I'm conneting to a ODATA source dynamics D365 entity which is exactly the same in both TEST and PRODUCTION

During my report building I'm not intrested at all in all the data of the production. 
How can i get around this issue ? Anyone?! Please!!!

[MP_123]

@asocorro@Greg_Deckler

hi,

if i connect live to data source, can i change the report from dev to production?

actually i don't understand how everyone import the data! i just can't import my cube to powerbi due to memory issues.. live connection it's hard

[Anonymous]

"actually i don't understand how everyone import the data! i just can't import my cube to powerbi due to memory issues.. live connection it's hard"

completely agree. moreover, it's a dumb thing to do in most contexts, for Single Version Of The Truth reasons.

[asocorro]

I got his from some blog that I cannot remember now.  To be able to change the source info for all of your queries, create a new query that returns the connection string and then use it in the Source declaration of the others.  For example, if you have an Access database source, create a query call AccessFileAddress, like this:

let
Source = "C:\MyFolder\MyDatabase.accdb"
in
Source

and then in all of your queries that need to connect to the database, use it like this:

let
Source = Access.Database(File.Contents(AccessFileAddress))

Then when deploying you only need to change the definition of the AccessFileAddress query.

[Anonymous]

How does this work if you have a sql server connection?

[Panoone]

@asocorro you, sir, are a legend.

[Greg_Deckler · Accepted solution]

You have to edit your query and edit the first line of your query "Source" So, "Edit Queries", choose your query, next to Source step, click the gear icon.

[chr9219]

Works perfectly!! 

[jliu]

Thanks for the reply. I was able to change database connection individually using the way you described. I'm looking for a way to change the database connection for all the queries that use the same connection since we have so many queries defined. We used to deploy  powerpivot Excel templates to our customers. Each user can change the database connection in one place and then start to use the template. we would like to deploy PowerBI desktop PBIX files in future. We need to have an efficient way to change the database connection for our customers.

thanks again.

JL

[JulieGueho]

Thanks for sharing this issue. We also have challenges for the deployment of Power BI reports and dashboards for our customers. It'd be very interesting to have what's suggested here.