Validate if value(String) is a date or not using M Language

[Anonymous]

Hi 

I am tryingto cretae this on the PawerQuery editor, use M Language, to evaluate if a value, or string if we can call it a string, is either valid date value or not.  I was aiming to use a Custom Column to indicate the outcome if True, yes it is a date, or False opposite.  Now when I import my data its takes on the 'ChangeType' as ABC123, if I set the format to date I basically create errors for rows with text rather than a date, exampe are 'n/a' or other words/text or sorts.   I have tried Value.IS([field] Number.Type) ist not doing it.  I dont wish to overwrite the orginal values I just need to indicate/test if it is date or not.

[artemus · Accepted solution]

Your function would look like:

try Date.FromText([DateColumn]) <> null otherwise false

[Vijay_A_Verma]

Use this (replace Date Field appropriately)

=try Value.Is(Date.From([Date Field]), type date) otherwise false

If you want to convert it to date in a custom column

try Date.From([Date Field]) otherwise null

[Anonymous]

Hi 

formula...Value.Is(Date.From([Date Field]), type date)

This is partially working,  however if the value is random text, I get am error in that row.  I may need need rethink the solution.

Chris

[Vijay_A_Verma · Accepted solution]

You will need to use complete formula alongwith try to suppress the error.  Don't omit try.....

=try Value.Is(Date.From([Date Field]), type date) otherwise false

If you want to convert it to date in a custom column

try Date.From([Date Field]) otherwise null

[Anonymous]

Perfect, I now see how this works, yes it is working great and gives me a good starting point, thanks for the advice.

[artemus · Accepted solution]

Your function would look like:

try Date.FromText([DateColumn]) <> null otherwise false

[Anonymous]

Hi

Did not work for my situation but handhy to have options, thanks.