Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi all,
I'm pretty new to PowerBi and here's my situation, hoping to avoid this setup (or mistake) in the future:
- I created a report that connects to my local SQL server and its database, everything works and the report is done.
- I have published the report to a workspace.
- I have setup the on premise data gateway on another machine that is running the target SQL server and its database. This is configured and up and running on my PowerBi portal, all good on this part.
- If I want to refresh my published report it fails, but that's normal because there is no connection to the dataset.
- In the settings of the semanctic model the datasource refers to my local SQL database connection, lets say SERVER-A;Database1
Desired result:
The published report should be connected to a different SQL server (SERVER-B;Database1), the one that is accessible through the gateway. The database remains exactly the same. I just worked on a copy of it on my local machine.
How can I point my report to the correct server to fetch its data?
Much appreciated if anyone could help 😎
Thanks!
Solved! Go to Solution.
Hi,@unknown_object .I am glad to help you.
If you want to modify the data source of a report, whether it has been published to the service or not.
You can change the data source information via power query (transform data>Data Source settings)
Or you can modify the data source by modifying the M code directly through the Advanced Editor (I recommend this approach).
Luckily, I also found other similar issues that have been resolved, so I hope that helps.
URL:
Solved: How to Change Data Sources for Existing Power BI R... - Microsoft Fabric Community
Solved: Changing Data Source - Microsoft Fabric Community
like this:
Here are my tests:
I have two SQL Server databases with a table of the same name (you need to make sure that you don't have a huge architectural change after your data source change to avoid reporting errors in your reports)
First use databaseA as the data source to create a report
You can see that the table structure is the same but there is a difference in the data (the nowTime column is not the same).
Use AdventureWorksDW2017 as the data source to create the report:
Uploaded to service, configured gateway refreshed successfully (at this point the data source is AdventureWorksDW2017)
After modifying the data source AdventureWorksDW2022, click Refresh Preview
The data has changed
You can see that after uploading to the service, it shows that the data source credentials can not be verified through the gateway, at this time to reconfigure the gateway connection to the new database connection
After the configuration is complete, the refresh is successful and the service is displayed normally without reporting errors
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,@unknown_object .I am glad to help you.
If you want to modify the data source of a report, whether it has been published to the service or not.
You can change the data source information via power query (transform data>Data Source settings)
Or you can modify the data source by modifying the M code directly through the Advanced Editor (I recommend this approach).
Luckily, I also found other similar issues that have been resolved, so I hope that helps.
URL:
Solved: How to Change Data Sources for Existing Power BI R... - Microsoft Fabric Community
Solved: Changing Data Source - Microsoft Fabric Community
like this:
Here are my tests:
I have two SQL Server databases with a table of the same name (you need to make sure that you don't have a huge architectural change after your data source change to avoid reporting errors in your reports)
First use databaseA as the data source to create a report
You can see that the table structure is the same but there is a difference in the data (the nowTime column is not the same).
Use AdventureWorksDW2017 as the data source to create the report:
Uploaded to service, configured gateway refreshed successfully (at this point the data source is AdventureWorksDW2017)
After modifying the data source AdventureWorksDW2022, click Refresh Preview
The data has changed
You can see that after uploading to the service, it shows that the data source credentials can not be verified through the gateway, at this time to reconfigure the gateway connection to the new database connection
After the configuration is complete, the refresh is successful and the service is displayed normally without reporting errors
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Thanks for the extremly detailed post 😀
In all my attempts I was convinced that I had done these different approaches, maybe not in the right sequence...and mixing different guides or possible solutions.
But I started over following your instructions and I managed to get it to work.
All up and running now!
Thanks
Also in addition, I have already tried the <File - Options and Settings - Data Source Settings> in PowerBi desktop to change the values but then the report want to apply the changes and fails because I don't have access to the remote SQL server, therefore I cannot publish this updated version to my workspace (I think).
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
7 | |
4 | |
2 | |
2 | |
1 |
User | Count |
---|---|
7 | |
6 | |
6 | |
4 | |
3 |