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
Highlighted
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.

Highlighted
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 474 members 4,768 guests
Please welcome our newest community members: