Forum Discussion
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 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.
Your function would look like:
try Date.FromText([DateColumn]) <> null otherwise false
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
6 Replies
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
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
Your function would look like:
try Date.FromText([DateColumn]) <> null otherwise false