Forum Discussion
Solved
Confused 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!
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.
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.
Thank you for your courteous response. Your answer is much simpler than I had been advised. Once I get a working connection to the SQL Database from desktop I will be able to follow your process.
Ross
