Test for a Date in Power Query Language M

[Vijay_A_Verma]

Use case - In one of my blog post, I talked about how to test for a number in Power Query - Test for a Number in Power Query M Language.

This blog post takes the same kind of problem statement and checks whether given data is Date or not.

When we use dates, it is dependent upon the locate setting of your Power BI. Hence, if my locale is set to US (English), then 10/15/2022 is a valid date but 15/10/2022 is not a valid date as US follows MMDDYY format. If my locale is set to UK (English) then 15/10/2022 is a valid date but not 10/15/2022. 

Power Query is also intelligent to recognize date in ISO format i.e. in YYYYMMDD format as valid date irrespective of locale.

Solution - To check whether given data is date or not, you can use following formula where Date is column name.

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

Below are sample cases for English (US) i.e. MM/DD/YY format

You can use below code to perform a test for above sample

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY3BDYBACAR74e0FlkiiLdgC4WED1//TIyaCvx1ms7iTsZ6sokqxOeG6Z4/f3RjVyjD2AXsJLadRGYrCtdN1zhYJGuAnrDZY1v9ybHxQxAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try Value.Is(Date.From([Date]), type date) otherwise false)
in
    #"Added Custom"