Forum Discussion
RossChevalier
Helper III
Helper IIIConfused about accessing SQL Server
Hello, I am finding connecting to a SQL Server to be very confusing. I have an existing set of reports and dashboard published to the PowerBI service. The data sources are on my local machin...
- Hi there
Whilst it does appear to be hard, it can certainly be done.
Very often it is all about planning for and testing how you want to change from Excel to a SQL Server database.
Make sure that you already can connect to the SQL Server Source.
What you can do is in your PBIX create a new connection to the SQL Server and load the table in Power Query Editor
Once the table is loaded you can then go into the Advanced Editor and copy and paste the M code into your Excel table. This should then have changed the source from Excel to SQL.
Repeat this for all the tables in Power Query. once you are done, if you click on Data Source Settings you should only see a data source for your SQL Server. If that is working, you can then load your dataset, upload it to the Power BI Service and then connect it via the Gateway. Hi RossChevalier ，
Yes,for sql server,it can only be connected in desktop then published to service.
Here is the reference.
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
v-kelly-msft
Community Support
Community SupportHi RossChevalier ，
Yes,for sql server,it can only be connected in desktop then published to service.
Here is the reference.
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
RossChevalierThank you for taking the time and for providing the link Kelly. I had been using this tutorial but have been unsuccessful in connecting the on premises SQL Server as I am remote and even when connected by VPN, I am unable to connect to the named server.
Thanks
Ross