problem with date format

[Anonymous]

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!

[edhans · Accepted solution]

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. 

[Anonymous]

it works 

thanks a lot

[edhans]

Great @Anonymous - glad I was able to assist.

[amitchandak]

@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 ?

[Anonymous]

I don't want to make changes via ecel , only via power bi so it will be automatic when I will get more

[amitchandak]

@edhans , @ImkeF , Can you suggest come code for this. Values are in excel date format - both number(date as number) and date