The Issue

I have some long standing PowerBI reports that I use for summarizing project data. They use a variety of data sources, including Azure hosted SQL instances. I recently moved the Azure hosted SQL databases to a new instance as part of a major tidy up of my Azure resources. This of course caused my reports to break.

I thought swapping the SQL connection details in PowerBI would be easy, and I guess it was, but it took me too long to work out how.

The main issue was that my reports were quick ‘hacked together’ reports, just pushed to my PowerBi personal workspace. I had never considered a formal publication with shared data connectors and gateways. This had come back to bite me.

The Solution

You can’t just change the dataset (now called the semantic view) in the PowerBI web UI, the docs say you should be able to, and I could for a newly created report, but not my very old ones! I guess they must be using some older schema?

After many false starts trying to use the web UI, I ended up using the PowerBi Desktop as follows

  1. Open my PowerBI workspace in the browser
  2. Download each of my reports as .PBIX
  3. Edit the .PBIX in the PowerBI desktop app
  4. Go to ‘Transform Data’ (on the top menu bar), a new Window appears
  5. Go to ‘Data source settings’ (on the top menu bar), a dialog appears
  6. Press the ‘Change Source…’ button, and enter the new connection details
  7. When the ‘Change Source…’ dialog is closed you will see a banner message ‘Reauthenticate and reapply’. Press the button to start this process
  8. Your local copy of the report should now be using the new data source
  9. Save the updated .PBIX, I used a new name but this is not essential
  10. Upload the revised .PBIX to your Power BI Workspace.

So I got there in the end. I am sure there are easier ways. I am just not familiar with PowerBI enough to know them (yet)