I am having some issues with filtering a column by dates when connecting to SQL Server 2005. Whenever I try to filter by dates in the query editor >= 01/01/2015 I get an error:
DataSource.Error: Microsoft SQL: Type datetime2 is not a defined system type. Details: DataSourceKind=SQL DataSourcePath=1552ts;tmsdata Message=Type datetime2 is not a defined system type. Number=243 Class=16
After a brief google search it looks like the culprit is SQL Server 2005 and Power Query possibly don't play nice together with datetime values. Does anyone know a way of filtering these dates and still have the query fold back? Because of the size of the table I am trying to keep it from pulling everything down and then transforming.
The only other option I see at this time is to send a SQL statement with the query but from my understanding that would prevent the rest of my transformations from folding and while my SQL is decent I have a hard time with joins on multiple tables.
Do you have any control over the database you're connecting to? If so you could look into adding a view on top of the data source and converting the date into text or an integer (or joining to a date dimension) and filtering on that instead.
I have a report developed that is attached to a dashboard. I use a SQL database as the source of my report. The version is SQL Server Express Edition 9.4.5000.00 and it is the backend of an IT ticketing system. I am able to pull data, publish and refresh from the Desktop. I have scheduled the dataset to refresh. The scheduled refresh will not complete on the scheduler and I cannot make it refresh.
I get Underlying error code -2147467259 Table:TASKS
Underlying error message Microsoft SQL: Type datetime2 is not a defined system type