Changing data sources in Power BI/Query Editor

Power Query
1

Hi All,

I have build a data model and report by connecting to excel. but my source database is going to be Oracle or Sql server.

Is it possible to change the data sources in my model and upload to services so that data refresh can be managed with the gateway?

Please can some help with this and let me know whats the best way to do it

Thanks in advance.

Swapnil

2

Yes absolutely and it’s seriously simple to do it.

You have to make sure though that the data is exactly the same structure. Any changes and this won’t work.

All you have to do is create a new query to you sql database. Once you’ve done this, you then have M code that you can sub into other queries. So you just then replace the initial M code into the existing excel table.

See some image of where to do this.

image
image.png1130×444 26.8 KB

Just change the top part here to your sql database query and then you’ll be all set.

All this happen here in the advanced editor.

image
image.png775×300 25.8 KB

Thanks
Sam

1 Like
3

Thanks for the response.

Just another linking question. When we change the connection in the M code, after saving it, does it reload data of all tables in the model?

I don’t want all tables to get reloaded in the desktop but only should happen once published to service and enable the gateway. Is that possible?

Thanks in advance

4

Well I think it will update the data, because that’s going to be the new data source.

Potentially you could change the M code, and then not refresh the data. Then upload to the online service. I would give this a go and see if it works. I’m not actually sure myself as I’ve never had a situation like that.

Thanks
Sam

5

Thanks sam.i will be waiting for it. Thanks again

6

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.