Using Where in SQL statment in PowerQuery

Question

Im creating an excelfile with data from a SQL server. I have no problem establishing a connection but when I enter the following SQL statement the part after Where seemes to be ignored.

Select *
FROM dbo.IMP_ELEMENT
Where dbo.IMP_ELEMENT.CHANGED_DATE <'2018-01-01';

This causes the excelfile to retrieve all the rows in the database which makes it very slow.

I've tried to rename dbo to the actual databasename and tried to use VBA as well but neither worked out particarly well.

This is the used code in the PowerQuery Editor:

= Odbc.Query("dsn=IMPACT", "Select * FROM dbo.IMP_ELEMENT 
Where dbo.IMP_ELEMENT.CHANGED_DATE <'2018-01-01'")

I expect The Query to only generate rows newer than 2018-01-01. And I don't want recieve all data and then filter it in Excel as that is very slow with a large database. Anyone got any idea?

I've encountered databases where a date field is formatted (in SQL) as text instead of date/datetime. Have you checked that .CHANGED_DATE is a date/datetime column? — Mistella
yes its a datetime column and the filter works in sql management studio — Patrick Bender
Well that was embarrasing... just needed to invert the larger sign... — Patrick Bender

Patrick Bender Patrick Bender · Accepted Answer · 2019-05-01T18:58:15

Well that was embarrasing... just needed to invert the larger sign...

Using Where in SQL statment in PowerQuery

1 Answers