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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.