Change Data Source

[Dilantha]

Hi,

Initially when creating power bi report I retreived data from dev database environment,  
And now I need to point to the staging environment, On premises gateway is setup from organization and I have to point DB to the power bi report.
I setup it from gateway clustors. When it comes to the gateway I cannot find dropdown of databases. It still displays add to gateway message as below.

But in the clustors required database environments are conectected and its online.

Do I need to change it from advance editor or can I do it from here?

Thank You.

[TomMartens]

Hey @Dilantha ,

you have to change the data source in Power BI Desktop and point the desktop file to the production database.

Then publish the file to the workspace.

Now you can select the data source that you have configured in the service using the gateway.

Hopefully, this provides what you are looking for.

Regards,

Tom

[Fahima]

But when ever we changed the data source in the data source setting.Don't we loose the data changes that we done in power Query editor.

[Dilantha · Accepted solution]

Hi, This is how I figured out ,

First I followed these steps.
(Open query editor -> select table -> double click source -> change source )

I changed source each table one by one.

5. Open Advanced Editor.
6. Change Schema to the required database.

Thank You

[Dilantha]

Hi, 
I had error like below,

Im getting data from mysql views and no external files used. I added views to staging db as well.
Can you help me to fix this. I closed all database related windows after change as suggested by earlier post,

Thank You

[amitchandak]

@Dilantha , The error in the last post comes when their column removed or view/table is not present in Database.

Refer if this can help https://www.youtube.com/watch?v=h0yLtceT0i4

[Dilantha]

Hi,
I went through that vedio. 
I have already connected to dev environment and I want to connect staging environment, can we do that from gateway ? If we can do that its much easy.
If I change data source Im loading same preview with changes --> New Data source.
I cant revoke previous changes because its very time consuming. 
I wanted to publish dashboard with 2 different database.  

Thank You

[amitchandak]

@Dilantha , You can create a copy of the pbix and publish the new one with the production database?

[Dilantha]

Hi, 
any luck for the issue ?
Most examples about changing excel files, their is no examples for how to change entire database.

Do I need to create new pbix with staging database connection ? 

[Dilantha]

Currently I can not create tha.
1. What I hve in mind was select database environment from gateway and save, then we can see the visualizations with different environment (staging, prod). Is that possible to do ?

2. Can we do the below process ?

create dashboard 
change datasource A 
publish - development testing purpose
change datasource B

publish - qa testing purpose

change datasource C 

publish - deliver to the customer

3. if we can do above process how to do that? 
4. from gateway or desktop?

Thank You

[amitchandak]

@Dilantha , Add remove coonection in gateway -https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-data-sources

or -https://radacad.com/the-power-bi-gateway-all-you-need-to-know

Edit connection in power Query/power bi - https://www.youtube.com/watch?v=59PUFuuCrbY&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L