Forum Discussion

Giel0's avatar
Giel0
Frequent Visitor
3 years ago

Filter on date column with SQL ibm db2

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!

 

 

 

 

 

 

 

 

Need Help

3 Replies

  • alosada's avatar
    alosada
    Frequent Visitor
    3 years ago

    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!

  • Giel0's avatar
    Giel0
    Frequent Visitor
    3 years ago

    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's avatar
      alosada
      Frequent Visitor
      3 years ago

      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.