How to Check If Text is Date in Power BI Power Query

One of my colleagues asked me how to check if a text value is a date in Power BI Power Query. He was working on a report where a date field was stored in text format. When he tried to change the column data type to Date, Power BI showed errors for some rows.

The issue was that the column contained a mix of valid dates and text values. Because of this, the report was failing whenever date functions or filters were applied.

To solve this problem, I used Power Query to check whether each value is a valid date before converting it. By using a custom column, we can easily identify date values and handle text values without breaking the report.

In this Power BI article, I will show how to check if text is a date using Power BI Power Query.

Check If Text is Date in Power BI Power Query

Now, I will show how to check if text is a date using Power BI Power Query with two simple examples.

In the first example, we will check whether a text value can be converted into a date and return TRUE or FALSE based on the result.

In the second example, we will check if the value is already a date and return the date value; otherwise, it will return null. This approach is useful when you want to keep only valid date values and ignore incorrect text data.

Example :1 Check If Text Is a Date Using a Custom Column

In this example, I am using a Vehicles table as the data source. The table contains a Released Date column where some values are stored as text, and some are stored as date values.

Now follow the steps below:

1. Open Power BI Desktop and load the dataset using the Get Data option. After the data is loaded, go to the Home tab and click Transform data. This will open the Power Query Editor.

2. In the Power Query Editor, select the column where you want to check whether the value is a date. In my case, this column is the Release Date.

3. Then go to the Add Column tab and click Custom Column to check whether the value in the column is a date or not.

4. In the Custom Column window, enter a suitable column name, add the required formula to check if the value is a date, and then click OK to create the new column.

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

After clicking OK, Power Query adds a new custom column. This column shows TRUE for values that can be converted to a date and FALSE for values that are not valid dates.

To confirm that the custom column is working correctly, change the column data type from Text to Date by selecting Home -> Data type -> Date. After this change, the values that are valid dates will be converted successfully, while the text values will show an error.

At the same time, check the custom column results. You will see TRUE for rows where the value is a valid date and FALSE for rows where the value is text. This makes it easy to verify that the custom column is correctly identifying date and non-date values.

Example:2 Check If Value Is Date or Text in Power BI

In this example, I am using a Bikes table to show a slightly different scenario. In this table, the Released Date column contains both date values and text values. You can refer to the screenshot below to see how the data looks before applying any transformation.

Here, I want to return the date value if it is a valid date; otherwise, return null.

To do this, follow the steps below:

1. Load the data source into Power BI Desktop, then go to the Home tab and click Transform data. This will open the Power Query Editor, where you can start working with the data.
2. Now, in the Power Query Editor, you can see that the Released Date column contains a mix of Text and Date values, as shown in the screenshot below.

3. Select the Add Column tab and click Custom Column to check whether the value in the column is a date or not, as shown below.

4. In the Custom Column pop-up window, enter a new column name, paste the formula shown below, and then click OK to apply it.

if([Released Date] is date) then [Released Date] else null

Then, you can see that the custom column displays null values for the text data type. Check the screenshot below.

Now, change the column data type from Text to Date by selecting Home -> Data type -> Date. After changing the data type, you will notice that valid date values are converted and displayed correctly, while non-date text values return null.

Similarly, the custom column also reflects this logic by showing the date value for valid dates and null for text values, based on the condition applied.

This is how to check if the text is a date using the Power Query editor in Power BI.

In this article, I covered how to check whether a text value is a date in Power BI Power Query. I covered two examples: one shows how to return TRUE or FALSE when a text value can be converted to a date, and the other shows how to return the date value or NULL based on the data type.

You may like the following Power BI tutorials:

• Select Multiple Values in Power BI Slicer
• Add Column Using Power BI Power Query Editor
• Extract Text in Power BI Power Query Using Add Column
• Best Way to Count Rows and Distinct Values in Power BI Power Query