Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Dilantha
Post Patron
Post Patron

1899-12-31 in time column and 0.00 in date column

Hi Guys,

I have question about date and time field results after downlod csv file. I duplicate created_at column and changed data type as one column date and another as time.

  • When view data point as table data viewed in perfectly.

  • On data view mode alsi i couldnt find any issue, i also tried to change with column tools -> format , After change from this way it apply correctly,

  • My question is when I download csv file its date and time column is change as this

Dilantha_0-1606152903014.png

  • I tried to change format from exel file, but it need to download as

Dilantha_1-1606153001180.png

Is their anyway to fix this, Struggling from days.

Thank you

1 ACCEPTED SOLUTION
v-yuaj-msft
Community Support
Community Support

Hi @Dilantha ,

Base on your description, I think it is because the export function of Power BI is not smart enough to automatically determine what type to list, and will become custom or general by default. The default date of time is 1899-12-30, and it will be displayed after exporting. Just as the default date of date is 0:00, hidden ones will be displayed.
Sorry for that you can only manually modify the format in Excel.
My test process is as follows, I hope this can give you a little help.

 

1. my test data in Power BI Desktop.

v-yuaj-msft_0-1606900940594.png
2. exported data in .CSV file

v-yuaj-msft_1-1606900993494.png

3. change the Format of "Date"(Enter "hhhh-mm-dd h:mm" in the "Type" cell)

v-yuaj-msft_2-1606901084133.png

4. change the Format of "Time".
(Because it is in "General" format, I split the column first and then convert the Time column to "Time" format)

v-yuaj-msft_3-1606901546756.png

v-yuaj-msft_4-1606901603840.png

Then remove the unwanted column.
Result:

 

v-yuaj-msft_6-1606901883427.png

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yuaj-msft
Community Support
Community Support

Hi @Dilantha ,

Base on your description, I think it is because the export function of Power BI is not smart enough to automatically determine what type to list, and will become custom or general by default. The default date of time is 1899-12-30, and it will be displayed after exporting. Just as the default date of date is 0:00, hidden ones will be displayed.
Sorry for that you can only manually modify the format in Excel.
My test process is as follows, I hope this can give you a little help.

 

1. my test data in Power BI Desktop.

v-yuaj-msft_0-1606900940594.png
2. exported data in .CSV file

v-yuaj-msft_1-1606900993494.png

3. change the Format of "Date"(Enter "hhhh-mm-dd h:mm" in the "Type" cell)

v-yuaj-msft_2-1606901084133.png

4. change the Format of "Time".
(Because it is in "General" format, I split the column first and then convert the Time column to "Time" format)

v-yuaj-msft_3-1606901546756.png

v-yuaj-msft_4-1606901603840.png

Then remove the unwanted column.
Result:

 

v-yuaj-msft_6-1606901883427.png

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Dilantha , set the time data as time only. and set format as hh24:mm:ss and then try. Do this in column property so that it applies everywhere.

Data Format New Rib.png

I already tried this way, Its convert in everywhere except csv file. 

Fowmy
Super User
Super User

@Dilantha 

In Power Query, under Add Column Tab, click on the Date and choose Date Only to extract the Date for your date column. It should give you the expected format.

Fowmy_0-1606153991187.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thank You,  Im sorry to say, It didnt work . And I want to modify same column, because I added date to the global slicer.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.