Forum Discussion
Running SQL Queries in Excel
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.
Hi Dave7
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:
Note:
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.
Hi, Wyn and Dave7 I know this is an old post but not sure if you resolved this I tried to write the script in the previous post but got this error.
Let me know what you think of this error, please.
Thanks in advance.
That's on server site - certificate used is not recognised, thus connection can't be installed.
