Forum Discussion
problem with date format
Hello,
I am working with dates that are stored as text. I changed the type to date/time but not all the cell are translated to date/time.
I have error in the power bi transform data with this kind of cell 43562.09275
When I try to do it in the excel via Format custom, it does work, like in the picture:
I tried to do custum colum in power bi with custumed= FORMAT([Time], "mm/dd/yyyy hh:nn:ss AMPM"),
when I do this
but I got this:
What should I do in the transform data to get all the date time to be in the right format?
Thanks a lot with this!
This should work Anonymous in a new custom column in Power Query
try DateTime.FromText([Column1]) otherwise DateTime.From(Number.From([Column1]))Then just rename it Date, and change the format to DateTime or Date if you don't care about the time. Make sure the first column is in text. You have data coming in in different formats so Power Query cannot do one single change to all of it to work as you'd like.
6 Replies
This should work Anonymous in a new custom column in Power Query
try DateTime.FromText([Column1]) otherwise DateTime.From(Number.From([Column1]))Then just rename it Date, and change the format to DateTime or Date if you don't care about the time. Make sure the first column is in text. You have data coming in in different formats so Power Query cannot do one single change to all of it to work as you'd like.
Great Anonymous - glad I was able to assist.
Anonymous , if data is coming from excel. Correct the format there and try. Seems not all in same format at sources.
What is data source ?
