Power Query Check If Text is Date

by

In this Power BI article, we will learn how to check if text is Date using the Power Query editor with different examples in Power BI. 

Power Query Check If Text is Date

Let us see how to check if the text is date using the Power Query editor in Power BI.

Example :1

In this example, we will use the below Vehicles table data as a data source to check if the text is date and then returns the true value or else a false value in Power BI.

Power Query Check If Text is Date
Power Query Check If Text is Date

Open the Power Bi desktop and load the data into it, and under the Home, tab selects the transform data option as shown below: It will automatically redirect to the Power Query editor.

Power Query Check If Text is Date example
Power Query Check If Text is Date example

Now in the power query editor, you can see that the released date column data is presented with the text data type and date data type value as highlighted below:

Example of Power Query Check If Text is Date
Example of Power Query Check If Text is Date

Add a custom column by selecting Add Column-> Custom column check if the text is date or not as highlighted below:

Check If Text is Date in Power Query editor
Check If the Text is Date in the Power Query editor

In the custom column pop-up window, enter the new column name and add the below-mentioned formula and click on the ok button.

=try Value.Is(Date.From([Released Date]),type date) otherwise false
Example of Check If Text is Date in Power Query editor
Example of Check If Text is Date in the Power Query editor

In the below screenshot, you can see that the custom column displays the result value as true for the text data type and for the date data type it displays the false value.

= Table.AddColumn(#"Changed Type", "Custom", each try Value.Is(Date.From([Released Date]),type date) otherwise false)
Check If Text is Date in Power Query editor example
Check If Text is Date in Power Query editor example
  • Change the text data type to date data type by selecting Home -> Data type and selecting date data type, now we can see for the date data type displays the true value, and the text data type displays an error.
  • In the same way, the custom column displays the result value as true for the date data type, and for the text data type, it displays the false value.
= Table.TransformColumnTypes(#"Added Custom",{{"Released Date", type date}})
Power Query editor Check If Text is Date
Power Query editor Check If Text is Date

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

Example:2

In this example, we will use the below-mentioned bikes table data for the different scenarios to check if the text is Date in Power BI.

check if value is date or text in Power BI
check if the value is date or text in Power BI
  • Load the data source into the Power BI desktop, and select Home -> Transform data it will automatically redirect to the Power Query editor.
  • Now in the power query editor, you can see that the released date column data is presented with the text data type and date data type value as shown below:
check if value is date or text in Power BI example
check if the value is date or text in the Power BI example
  • Select Add Column-> Custom column to check if the text is date or not as highlighted below:
  • In the custom column pop-up window, enter the new column name and add the below-mentioned formula and click on the ok button.
if([Released Date] is date) then [Released Date] else null
Example of check if value is date or text in Power BI
Example of checking if the value is date or text in Power BI

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

= Table.AddColumn(#"Changed Type", "Custom", each if([Released Date] is date) then [Released Date] else null)
check if value is date or text in the Power BI
check if a value is date or text in the Power BI
  • Change the text data type to date data type by selecting Home -> Data type, and choose the date data type now we can see for the date data type displays the date value else it displays the null value,
  • In the same way, the custom column displays the null value and date value based on the condition applied.
= Table.AddColumn(#"Removed Columns", "Custom", each if([Released Date] is date) then [Released Date] else null)
power query if column contains date then
power query if the column contains a date then

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

In this Power BI article, we have learned how to check if text is Date using the Power Query editor with different examples in Power BI.

You may like the following Power BI tutorials:

>