Filter on date column with SQL ibm db2

[Giel0]

Hi,

I have a large table which I would like to filter dynamically based on a date column. I have an orderdate column which has the format  YYYYMMDD from the IBM db2 database. 

However, if I would like to transform this column to a date, I get an error:

SQL query:

I get a result in the Power Query editor:

However, when I click on close & apply this error occurs:

Does anyone has an idea on how to fix this? Tried a lot but nothing works..

My end goal is to compare the orderdate in the correct format with the date based on current timestamp: date(current timestamp). 

Thanks in advance!

[Giel0]

Hi @alosada ,

Thanks for your response. Loading the whole table and make transformations in the Power Query editor works. However, the reason for the SQL query is because it is a large table while I only need a small part of it. So importing the whole table gives me a lot of data which I do not need and could potentially lead to performance problems. 

So to make the data load as efficient as possible I would like to query on the small part I of the data in the table I need.

Is there any way to use SQL to exclude the rows which cause this error?

[alosada]

Hello @Giel0 ,

I don't think there's a way to use SQL to exclude the error rows, but here what I would do is filter the data that you need to keep with an SQL query and then trying to apply changes in PowerBI and see its behaviour.

[alosada]

Hello,

by the error it seems that there is a value in the column that cannot be converted to Date and that's what giving you the error.

Why don't you try importing the table with the column as it comes and trying to transform it in DAX?

If you do so, and need help, i will be glad to help you!