Export table to CSV adds Time to Date and Date to Time columns

[Anonymous]

Hi, 

Whenever i export my table to csv, it addes a time ("00:00:00") to my date fields and date ("1899-12-30") to my time fields. 

i created the date and time fields in the Query Editor:

= Table.AddColumn(#"Added Time Initial Talk", "Time: Talktime", each if [talktime] is null then null else #time(0,0,0) + #duration(0,0,0,[talktime]))

And after creating the column i change the type to 'time'. Since the may update i was experiencing time format issues, so i changed it to local time format US. The time is correctly displayed in the reports and in the online service. But when I export it to CSV, it adds the date in front of it. 

In excel, after transforming csv to excel file, I can change the format of the dat column to date and it removes the time. But with the time column thats not possible, it doesn't change anything. 

any ideas?

[Shelley]

Hi All, I know this post is old, so perhaps you found an answer. I too am facing this issue, and I found this post:

https://community.powerbi.com/t5/Desktop/1899-12-31-in-time-column-and-0-00-in-date-column/m-p/15282...

[v-yuta-msft]

@Anonymous ,

Could you please share some sample data for further test?

Regards,

Jimmy Tao

[Anonymous]

Hi Jimmy, 

my apologies for my delayed response, holiday times. 

I'm not able to share a sample report, but i do have the query below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1QGS5hDKFEwZmYEpYzMI19TMEkJbQCgIz8wYotYEWc4Y1TSg/lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [seconds = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"seconds", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Time", each if [seconds] is null then null else #time(0,0,0) + #duration(0,0,0,[seconds])),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Custom", {{"Time", type time}}, "en-UK")
in
    #"Changed Type with Locale"

in case that doesnt work, just create a table (enter data) with a colum 'seconds' and fill it with random numbers.

[breinke]

I am having the same problem.  Were you able to find a solution?

Thanks.