PowerQuery IsDate function

C

CharlieHall

New member
Joined
Aug 23, 2016
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2016, 2010
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
 
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,
 
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
 
How about this:

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

(Untested)
 
I guess this means it's not in that M is for Data Monkey book I just purchased from Amazon...
 
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".


Code: 
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"
 
You must log in or register to reply here.
Back
Top