How to change db connection from SQL Server to Power BI Service?

[dancarr22]

Hello,

We currently have multiple Power BI workbooks which connect to a SQL Server db.

I want to update the connection in these workbooks to point to a Power BI Service.  How is this done?

I've tried to go into the Advanced Editor in the Power BI Service to view the connection string but unable to edit queries.

Any help greatly appreciated.

Thanks,

Dan

[mow700]

You won't be able to edit queries if the source is a Power BI Service hosted model.  The trick to this is to promote this hosted model to a master structure.  Reports that reference this model as the source should simply filter the master model.  Your master model still connects to the original sources, you would be editing the master model queries if required.

More details:

https://powerbi.microsoft.com/en-us/blog/connecting-to-datasets-in-the-power-bi-service-from-desktop...

[dancarr22]

Thanks for your response.  I'm clear on the benefits of Power BI service and we are currently connecting to it for new reports.

But, we have older PBI reports which originally connected to SQL Server (on which the current Service structure is based) but there is no way to change the data source and say "point to Power BI Service instead of SQL Server".  

Is there any way in the Advanced Editor to change a SQL Connection to point to the service?

Thanks,

Dan

[mow700]

I get it now.  Confirmed, I don't see a way to change the datasource from SQL to PBI Service.  With a Service connected .pbix file I have no access to the query editor at all so I can't discover the connection syntax in the advanced editor.  I connected to the Service connected .pbix with DAX studio, and was unable to view any metadata.  I connected to the .pbix file with SSMS and could not enumerate any cubes or connections either.  Finally, I renamed my .pbix file with a .zip extension and inspected the contents of the Connections file that it contains with notepad.  Finally I can see the details, but I have not been successful in updating my SQL connected to file to the new source.  Good luck, please let us know if you discover a way to accomplish this one.