How to Replace a Data Source Connection With a Live Connection in Power BI

I recently ran into a scenario where I developed a Power BI report against a SQL database using a live connection, and later developed a Tabular cube that I wanted to use to replace the SQL data source. As it turns out, replacing one live data source with another is not as straightforward as it seems. In fact, when you try to add a new Live or DirectQuery connection to the report, the Live option is disabled unless you are replacing one SQL DirectQuery source with another DirectQuery (in this case the Import option will be disabled). I’m not sure the reason behind this behavior, but I found it a bit frustrating as it seemed the only way to leverage my new data source was to re-create my existing report from scratch.

Luckily, I found a workaround that saved me the headache of rebuilding my report, and hopefully it will save you time as well. Here’s what to do:

Document the Current Layout of Your Dashboard, and All the fields being used in the visuals

You can do this by either taking screenshots of the report and of the fields used in the each visual, or by doing a Save As and creating a copy of the report. This will give you a place to reference when mapping to the new data source in case any of the fields fail to map over.

Delete all the queries using your existing connection

From your Power BI report, click on Edit Queries to launch the Query Editor window. Right-click on each query (or use the Shift key to select multiple queries) and click Delete. Confirm your action and click Close & Apply to return to the report

Add your new data source

At this point all of your visuals will be broken, and that’s ok, we’ll fix it in the next step. Go to Get Data and select your data source. Choose the tables you want to load into your report and complete the steps to finish creating your connection

Map New Fields to Visuals

Depending on how the fields are named in your new data source, you will need to map the new fields to each visual. Use the copy of your report that you saved at the beginning (or the screenshots), to quickly see what the broken fields need to be replaced with in the new data source.

 

Hopefully this saves you some time the next time you have to switch data sources in your report!