locked
How do I validate values in a column of type date? RRS feed

  • Question

  • More than one of my query source files has rows containing assorted non-date (text or number) values in a date column. I need to remove all rows with invalid dates but cannot target them.

    I tried the following to no avail:

    = Table.SelectRows(#"Removed Columns", each [DueDate] is date)

    Is there a way to check if a value is a valid date?


    Saturday, December 15, 2018 11:39 PM

Answers

  • 2 options, depending on the actual format of your data. First attempts to transform the content to date first and then rules out the errors of that transformation. The other is an alternative way if the rows with correct dates are in date-format already. But the 1st formula would work for those cases as well:

    Table.SelectRows(PreviousStepName, each try Value.Is(Date.From([Column1]), type date) otherwise false)
    Table.SelectRows(PreviousStepName, each try Value.Is([Column1], type date) otherwise false)
    



    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, December 16, 2018 7:14 AM

All replies

  • a simple example in a given language is as follows:
    CREATE OR REPLACE FUNCTION F_DATE (v_date IN VARCHAR2) RETURN NUMBER IS
         v_date1 DATE;
    BEGIN
         select to_date (v_date) into v_date1 from dual;
             RETURN 1;
         Exception WHEN Others THEN
             RETURN 0;
    END;
    ----------------------------------------------
    SELECT F_DATE ('01 -JAN-09 ') FROM DUAL;
    - Returns 1
    SELECT F_DATE ('111111') FROM DUAL;
    - Returns 0...
    Sunday, December 16, 2018 12:21 AM
  • 2 options, depending on the actual format of your data. First attempts to transform the content to date first and then rules out the errors of that transformation. The other is an alternative way if the rows with correct dates are in date-format already. But the 1st formula would work for those cases as well:

    Table.SelectRows(PreviousStepName, each try Value.Is(Date.From([Column1]), type date) otherwise false)
    Table.SelectRows(PreviousStepName, each try Value.Is([Column1], type date) otherwise false)
    



    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, December 16, 2018 7:14 AM
  • Hi Imke

    If you don't mind… Assuming I don't need/have to keep current [Column1] type, from a performance perspective, what would you recommend between your above proposals and

    #"Changed Type1" = Table.TransformColumnTypes(PreviousStepName,{{"Column1", type datetime}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Column1"})
    

    Thanks

    Sunday, December 16, 2018 8:40 AM
  • Hi Lz._,

    you're welcome.

    Yes, your code might perform faster, as you're just checking "once" for errors, but you really have to try it out.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, December 16, 2018 9:13 AM
  • might perform faster, as you're just checking "once" for errors => Makes sense

    but you really have to try it out => As usual

    Asked as you obvioulsy have a lot of experience and between theory and reality there's often a gap. Thanks again
    Monday, December 17, 2018 7:46 AM
  • Thanks Imke!

    I used the top 'Date.From' snippet.

    Incidentally, this also removes rows where the Date column is NULL :)

    Monday, December 17, 2018 2:59 PM
  • Thanks Lz._

    I think your solution will work prior to a Changed Type step, but I used Imke's since the Date type was already in a previous step. I can't try this immediately but will come back and mark it as answered if it indeed works.

    Monday, December 17, 2018 3:02 PM