Forum Discussion
Switch a data source from SQL database to Power BI Service (live connection) on existing dashboard?
Hi,
Is is possible to switch a data source from an SQL database to Power BI Service (live connection) on an existing dashboard? I want to use the same data set but switch to live connection without having to recreate the whole dashboard. Is it supported by Power BI?
Thanks!
13 Replies
Hi, Was anyone able to reconnect existing SQL database dashboard to Power BI Service?
In theory, follow this procedure:
- Create a new PBIX file and connect it to the Power BI Service dataset that you want.
- In Query Editor, go to Advanced Editor and copy the Source line
- Copy your existing PBIX file to a new file
- Go into the Query Editor and the Advanced Editor and replace the Source line of your query.
Greg_Deckler but I cannot access the Query Editor once I chose the Power BI Service as data source, right?
Hmm, that's a fair point sophie63. If your original PBIX file connected to a live SQL Server, you might be able to do this.
- Create new PBIX file and connect to your dataset in the Service
- Save the PBIX file and then copy it to a .ZIP file
- Open the ZIP file and copy the "Connections" file
- Backup your original PBIX file or otherwise ensure it is safe and will not get overwritten
- Copy your original PBIX file to a .ZIP file with a rename for good measure -> copy MyFile.pbix MyFile1.zip
- Open this .ZIP file and replace the "Connections" file with the one you copied
- Rename this copied .ZIP file back to .PBIX
Hi sophie63,
I'd like to suggest you publish origianl report to power bi service, then create a new report with power bi service as datasource. It will change to live connection mode, but I haven't found a solution to keep original visuals.
BTW, I also test with smoupre's workaround, but it seems not works.
Regards,
Xiaoxin Sheng
I am able to do it just fine following the directions. The issue is the underlying metadata of the report. If you are trying to take a completely different report, and then point it over at a Dataset in the service, no, that will probably not work. If it does, the measures etc would all need to be re-written.
Anyone found a resolution to this ? Everyone starts with a Report + SQL Connected datasource initially then eventually evolves to PowerBI Dataset. It seems silly not to have a way to go from A to B without the need to totally recreate the original report.
This is the easiest way I have found. This assumes you have a SQL DB Connection of some type.
- Load the original .pbix file with the SQL DB Connection.
- I strip out the pages, and remove all visuals, leaving a blank page titled "Sales Dataset". Save this file as "Sales Dataset" and publish to the Power BI service. Close this file after publishing.
- Re-load the original report .pbix file
- Click on the Model view Icon to the left of the designer
- Highlight all the tables and delete them.
- Click on Get Data > Power BI Datasets
- Select the "Sales Dataset" you uploaded earlier. The report should connect. Click over to report view, and viola! Your visuals are working again.
- Publish this new report to your desired workspace
- Remove the old report
- Save file as .pbit (or as a .pbix but at this point .pbit is a better option)
Then you are doing something wrong. I just followed my own directions, and it worked fine. I just downloaded the latest version of Power BI as of today (June 30, 2023). That error sounds like you might be working with an older version of PowerBI Desktop that did not allow mixed datasets.
