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.

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

Super User I
Super User I

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors