Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
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.
Then export data and open in notepad
Measure 5 = CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(MONTH([Measure 3]),"/"),DAY([Measure 3])),"/"),YEAR([Measure 3]))
Best Regards
Maggie
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.
Then export data and open in notepad
Measure 5 = CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(MONTH([Measure 3]),"/"),DAY([Measure 3])),"/"),YEAR([Measure 3]))
Best Regards
Maggie
@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.
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:
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.
Proud to be a Super User!