Forum Discussion
Add a new datetime column from string
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
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 nullIf 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
4 Replies
rmocca , check for alphanumerics
use test function to get date
https://docs.microsoft.com/en-us/powerquery-m/text-functions
Thanks for the information.
I'll take a look ASAP.
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 nullIf 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 ForumHi,
Thanks for your help. I'll try this now.
Thanks again
