cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jliu Frequent Visitor
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

11 REPLIES 11
Super User
Super User

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

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

jliu Frequent Visitor
Frequent Visitor

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

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 Frequent Visitor
Frequent Visitor

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

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.

Highlighted
asocorro Member
Member

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

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.

 

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
chr9219 Frequent Visitor
Frequent Visitor

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

Works perfectly!! 

Microsoft MP_123
Microsoft

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

@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

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

"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.

BastiaanBos Frequent Visitor
Frequent Visitor

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

 
BastiaanBos Frequent Visitor
Frequent Visitor

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

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!!!

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)