Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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 Cus...
Need Help
  • artemus's avatar
    artemus
    4 years ago

    Your function would look like:

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

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

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