Custom SQL Query in Power Query Help

[denzo36]

Hi all,

I'm just wondering if anyone has connected to SQL server via a custom SQL query in Power Query? I've basically done this in Tableau but the report is very slow due to lots of calculations in Tableau so I would like to do it in Excel instead. I'd like some level of automation which is why I'm going for Power Query where I can hopefully refresh the connection each day. I'm not familiar with Power BI.

I can see that if you go the Data tab -> Get Data -> From Database -> From SQL Server Database , you get the below window. I'm having trouble with connecting correctly here

This is my work database so would I be right in saying the server name is the work server name, the database is the name of the database within that server?

Can the SQL statement be as complex as a normal SQL statement within SQL server studio? I had issues with custom SQL queries in Tableau and Tableau not accepting common table expressions but I'm assuming as Excel and SQL are both Microsoft applications there shouldn't be any syntax issues?

Can anyone provide some help to point me in the right direction?

 

[John_w]

The Server is usually <computer name>\<SQL Server name>, for example ABC1\SQLSERVER1. If you use SQL Server Management Studio it allows you to browse to a server and see the XXX\YYYY string.

The Database is whatever the database name is on the SQL server.

I don't know if the full range of complex SQL statements is supported. Excel's Power Query connection to SQL Server uses the Microsoft OLE DB driver, which determines the functionality of SQL statements.

 

[denzo36]

Hi John, many thanks for your response, I'll give that a go