Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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!

Need Help
  • edhans's avatar
    edhans
    5 years ago

    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.