PowerBI report - changing Data source from an ODBC to a SQL Server connection, best way?

[pennyjl]

We have had PowerBI for a while, however it was orginally setup using a personal gateway and an ODBC connection.    We are moving to a On Premise Gateway, and a SQL Server connection.     Now trying to figure out how I can change the currect report to use the SQL connection instead of the ODBC connection, so that I can get it to work using the on premise gateway instead of the personal gateway.     

I was able to get the gateway setup and connections to our data sources, but not able to add an ODBC connection to the on premise gateway.   was told that it would be better to use a SQL Server connection, which we added a connector for.    

So now wanting to modify the current report, and change the data source.       I have tried a few things but not seeing what I have to do, not finding any guide or instructions on what needs to be done, or if I can even make a change so this works, or if I have to create the report all over again.

Guidance would be greatly appreciated on this?     Not seeing this convered in any training courses either.

[Greg_Deckler]

@pennyjl Couple different approaches to this. If your queries essentially hard-coded the ODBC connection, you will want to go into Advanced Editor and replace your Source and probably Navigation steps to connect to your SQL Server. You would need to do that per query.

The better approach is if you have a separate query for your data source connection and that's all it does. And every other query simply references that query. Then you only need to do the edit in a single place.

So, essentially, I would create a query that connects to the SQL Server and database only. Copy the relevant lines in Advanced Editor and then edit your other queries as necessary replacing the necessary connection lines.