Add a new datetime column from string

[rmocca]

Hi,

I have a simple question:

I have a table with a column called Value, which is a string type. Some rows have a string DateTime, such as yyyy/MM/dd, but others have numbers or even alphanumeric codes.

I need to get a new column, converting the DateTimes and setting nulls for the rest of the rows.

It's that possible?

I appreciate your help.

Thanks,

Rodrigo Mocca

[edhans · Accepted solution]

Yes. Power Query is the best place to do this. Often the Date.FromText() function can handle a number of text formats.

• December 31, 2020
• 20201231
• 2020-12-31

Those are a few. If you add a new column in Power Query and use this, you will either get a date or a null.

= try Date.FromText([Your Text Date Field]) otherwise null

If you can post some sample data, we can help with formats that Power Query does't natively recognize.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum 

[rmocca]

Hi,

Thanks for your help. I'll try this now.

Thanks again

[amitchandak]

@rmocca , check for alphanumerics

https://community.powerbi.com/t5/Desktop/Checking-a-column-for-alpha-and-alpha-numeric-characters/td...

use test function to get date

https://docs.microsoft.com/en-us/powerquery-m/text-functions

[rmocca]

Thanks for the information.

I'll take a look ASAP.