topic Text will not convert to DateTime in DAX Commands and Tips

Text will not convert to DateTime

nhodges — Tue, 31 Jan 2023 15:24:47 GMT

Hello,

All the other DateTime fields in my data easily converted from text except for this one I am getting an error. It gives me error in powerquery when I go to convert it. How can I get this to DateTime?

Re: Text will not convert to DateTime

ValtteriN — Tue, 31 Jan 2023 15:48:39 GMT

Hi,

It seems your date is in ISO 8601 type datetime. Try using "DateTime.FromText".

E.g. = Table.AddColumn(Source, "Custom", each DateTime.FromText([Column1]))

Edit:
I noticed your year and month are in a different order than in my example. In that case this is a bit trickier.
Then you need to add optional variables. E.g.

= Table.AddColumn(Source, "Custom", each DateTime.FromText([Column1],[Format="yyyy-dd-MM'T'HH:mm:ss", Culture="en-US"]))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Text will not convert to DateTime

nhodges — Tue, 31 Jan 2023 16:08:03 GMT

Hi, im not sure how get to this option I tried to add a column in the main pbi view but it will not read the formula you gave me. So I assume I have to do it in the power query editor but cannot figure out how to add a column here with a formula.

Re: Text will not convert to DateTime

FreemanZ — Wed, 01 Feb 2023 02:55:23 GMT

hi nhodges

yyyy/dd/mm is very rare. do you know the country origin of this data?