Oct 27 2020 02:09 PM
Oct 27 2020 02:09 PM
I've used ODBC queries in Excel before but it was always with stored procedures on the server. Now I'd like to be able to define freeform SQL queries in Excel that pull data from a MS SQL Server. I have been playing around for 30 mins or so and Googling how to do it but can't seem to find anything. How do I build and debug SQL queries in Excel?
Oct 28 2020 05:58 AM
Thanks, @Wyn Hopkins. I should have said that yes, I got into the Power Query but couldn't figure out how to use it to write plain SQL. The Advanced Editor it has seems to use a different language (PowerQuery, apparently). Is it just not possible to write plain SQL? If that's the case, I'm sure I can figure out PowerQuery though if you have a good primer I'm all ears.
Oct 28 2020 01:06 PM
Power Query is not the tool for generating SQL query, you may only embed already prepared SQL query into the connector to SQL database. There are pros and cons, but mostly the latest. If only performance for relatively complex queries since SQL query runs on server site, but even in this case it's better to create view directly on the SQL server and connect to it.
In Power Query major part of tasks could be done with user interface without coding on M-script (internal Power Query language). Query to SQL database could be generated from UI as well.
Oct 29 2020 05:59 PM
When connecting to a SQL database you do have the option of writing plain SQL here...
If you use the Power Query interface to do transformations then if that transformation can be converted into SQL it will be done automatically in the background... You can often (but not always) see this happening by Right Clicking on a step and viewing the Native Query:
You may well get better performance by using the Power Query steps and allowing it to convert and "fold" much of the query back to the SQL Server.
Writing your own SQL query from scratch breaks "query folding" and the data processing will be carried out on your computer.
e.g. Filtering just for this year's records - if done via applying a filter in Power Query will push that filtering back to be performed quicker by the Server.