How to check for dates in Power Query and Power BI (Mimic IsDate)

by

In this article, we will show you how you can check for dates in Power Query and Power BI.

You may be used to using functions such as IsDate to check for dates in other languages, however Power Query and BI don’t have an IsDate function.

Nevertheless, it is still possible to check for date values, because we can mimic IsDate by using other functions.

You can also use these methods to check for number and text values in Power Query and Power BI.

Check for dates in Power Query

Here’s an example of a column in Power Query with some numerical and date values in.

We want to identify the date values in this column.

Note: your column can also have text values in and this method will work at identifying the date values.

Firstly, create a custom column in Power Query.

To mimic the IsDate function and find the date values, we need to use the Value.Is function as well as the Date.From function.

This will check the values in your column, return “Date” if the value is a date and return an error if it is anything else, (i.e. text or numeric value).

We have also wrapped the formula in a “try… otherwise” statement to turn the errors into “Not Date”.

try if Value.Is(Date.From([Dates]), type date) then "Date" else "Not Date" otherwise "Not Date"

This formula will then return either “Date” or “Not Date” for each row in the column. You can change these to return different values if you wish.

Here’s the result:

Check for dates in Power BI

You can also replicate this method and check for dates in Power BI.

Here’s an example of the same column from the previous section, this time in Power BI.

To mimic the IsDate function in Power BI, using DAX, we can use the DATEVALUE, IF and ISERROR functions.

The DATEVALUE function will return an error if the value is not a date. When we wrap it in an IF and ISERROR statement, it will return specified values for date values and non-date values.

In this example, it will return “Date” and “Not Date”. You can change these to return whatever you want.

Here is the custom column formula we would use in Power BI: 

IsDate = IF(ISERROR(DATEVALUE(Dates[Dates])),"Not Date","Date")

Here is the result:

Leave a Comment