Power BI Gateway Server issue

[Anonymous]

Hi Experts

in a nut shell (It’s not the gateway server which is moving, it’s the SQL server which the connections point to.  I can’t see how to re-point them to the new SQL  server.  The gateway server stays where it is.)

We are moving our data warehouse SQL Server a new server in a few weeks (from ABC111 to ABC-DW-001).

This means that all the Power BI datasets linked to databases on this server via the gateway will need to be re-pointed to the new server.

I had naively assumes that we would just be able to change the server name in the gateway data sources and it would be fine.

But not so, apparently – it doesn’t appear to be possible to change them. I then tried creating a new data source for the new sever (ABC-DW-001) and changing the mapping for a dataset in the test workspace to use this instead of the one it’s currently using (pointing to ABC111).

But that doesn’t work either. It won’t let me change the mapping to a data source pointing to a different server. HELP what am i doing wrong.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

You need to change the server name for all datasets to which this data source is applied. An easier way is to use the Power BI Rest API to modify the data source.

Datasets - Update Datasources In Group 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexanderPrime]

If you use Power Query Editor/Transform Data you should be able to do it, though you'll need to do it for every table in the file you are using, but if you select a table then go into Advanced Editor, usually the 2nd line is the Source location which you can edit. 

https://www.myonlinetraininghub.com/changing-data-source-location-in-power-query  for more info on how it works.