cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hammie Frequent Visitor
Frequent Visitor

Power bi export to CSV adds the time

 On Power Bi, my date column has the form of mm/dd/yyyy however when exported to CSV, it adds the time. How can I fix it so it only shows mm/dd/yyyy and not mm/dd/yyyy hh:mm:ss? 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Power bi export to CSV adds the time

Hi @hammie

After testing, I face the same problem with you.

A workaround I use is format the measure to a text value as"mm/dd/yyyy "

Look at the following picture, measure3 is a measure calculated from column[date] and [column], it is a date/time value.

The measure 5 converts the measure3 from date/time format to text format.

1.png

Then export data and open in notepad

2.png

Measure 5 = CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(MONTH([Measure 3]),"/"),DAY([Measure 3])),"/"),YEAR([Measure 3]))

 

Best Regards

Maggie

View solution in original post

4 REPLIES 4
RMDNA Senior Member
Senior Member

Re: Power bi export to CSV adds the time

@hammie,

 

Make sure your date column type is set to mm/dd/yyyy in the Query Editor.

hammie Frequent Visitor
Frequent Visitor

Re: Power bi export to CSV adds the time

@RMDNA Thank you but here is more info

 

this column is a measure I have created by using 2 different date columns. One column is mm/dd/yyyy and the other column I used to caluculate is mm/dd/yyyy HH:MM;SS. however i changed my result column format as mm/dd/yyyy and it shows as so. However when I export it to CSV, the result column is shown as mm/dd/yyyy HH;MM;SSS. But I don't want the time, just the date to stay on the CSV.

ChrisMendoza Senior Member
Senior Member

Re: Power bi export to CSV adds the time

@hammie,

 

Date & Time is really just a decimal and what you see in Power BI is a Formatted version of that number.

I'm guessing your export returns something like: 

 

DATEonly,TIMEonly,DATEandTIME
2018-06-11 00:00:00,1899-12-31 16:53:00,2018-06-11 16:54:12

 

 

I'm curious to know how you are returning the values as a Measure as I am getting an error.

 

I was able to create a Calculated Column as:

 

Column = 
VAR yr = YEAR(Table1[DATEandTIME])
VAR mo = MONTH(Table1[DATEandTIME])
VAR day = DAY(Table1[DATEandTIME])

VAR YrMoONLY = COMBINEVALUES("-",yr,mo,day)

RETURN YrMoONLY

which returns a TEXT verison that exports as:
2.PNG

 

 Of course, then when you re-import that 'number' 2018-06-11' you'll have to correct back into time format if it is required.

Community Support Team
Community Support Team

Re: Power bi export to CSV adds the time

Hi @hammie

After testing, I face the same problem with you.

A workaround I use is format the measure to a text value as"mm/dd/yyyy "

Look at the following picture, measure3 is a measure calculated from column[date] and [column], it is a date/time value.

The measure 5 converts the measure3 from date/time format to text format.

1.png

Then export data and open in notepad

2.png

Measure 5 = CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(MONTH([Measure 3]),"/"),DAY([Measure 3])),"/"),YEAR([Measure 3]))

 

Best Regards

Maggie

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 121 members 1,772 guests
Please welcome our newest community members: