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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.