PowerQuery IsDate function

[CharlieHall]

I am stumped - I would have thought this would be an obvious function in M - to check a field to see if the contents represent a date and do one thing if it is and do another if it is not (if then else)

But there is no IsDate function that I can find. Any alternatives that I am missing?

Thanks

 

[Ken Puls]

Try this:
-Duplicate the column of "dates". We'll call that column [IsDate]
-Convert the [IsDate] column to a date data type
-Replace Errors with null on the [IsDate] column
-Run your conditional logic: =if [IsDate] <> null then "it's a date" else "not a date"

Hopefully that helps,

 

[CharlieHall]

Thanks - I did find other alternatives, but doesn't it seem like a fairly useful function - it not only works in excel, but also works in SQL - and PowerQuery is meant to bridge those tools

and IsNumber and IsText don't exist either

I guess Value.Is might work, but it seems convoluted to use

 

[Ken Puls]

How about this:

= if (try Date.ToText([Column1],"yyyy-mm-dd") otherwise "No") = "No" then "No" else "IsDate"

(Untested)

 

[martingaleh]

I guess this means it's not in that M is for Data Monkey book I just purchased from Amazon...

 

[MarcelBeug]

Just read on and check pages 23/24.

 

[martingaleh]

OH, that's great news

Just read on and check pages 23/24.

My book is in the mail. It takes a week for some reason. Usually amazon prime is 2 days

 

[Rudi]

Here (stackoverflow) is a possible thread to scan for additional options...

 

[MarcelBeug]

In my view, that are different cases, i.e. check if a value is of a particular type.
In this thread, the question is if a string CAN be converted to a date.

If you use the standard menu option to parse a date from a string, the code will first try and parse a datetimezone and then take the date part from the result.
This way, all elements that may be included in the datetimezone format, may be in the string and result in a date in this 2 step approach.

And there is an important element involved: the culture. Some strings may be converted to a date with 1 culture code, but not with another.

In the example code below, the first example wouldn't convert to date with culture code "en-US".

let
    Source = #table({"String", "Culture"},{{"samedi 9 septembre 2017, 21:31","fr-FR"},{"Saturday, September 9, 2017, 21:31", "en-US"}}),
    #"Inserted Parsed Date" = Table.AddColumn(Source, "Parse", each Date.From(DateTimeZone.From([String], [Culture])), type date)
in
    #"Inserted Parsed Date"