Inconsistent date format in csv

[Anonymous]

Hello

I am loading a source and the dates appear as DD/MM/YYYY 00:00:00.

I then run the following: 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Complete Date", type date},...

Then, I also need to choose dd/MM/yyyy in the Modelling section to make the dates appear as dd/mm/yyyy in the dashboard table.

However, when I extract in csv, they appear as DD/MM/YYYY 00:00:00

Is there a way to fix that?

Thanks!

[PhilipTreacy]

Hi @Anonymous 

What is in the CSV?  If it's loading dd/mm/yyyy 00:00:00 into PQ then that is the data in the CSV?

Even after you do transformations in PQ to get rid of the time component, when the date is loaded into the model, a time component is added to the data because the model only understands a datetime data type.  It doesn't have separate date and time data types.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-types 

https://support.microsoft.com/en-us/office/data-types-in-data-models-e2388f62-6122-4e2b-bcad-053e3da9ba90

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[amitchandak]

@Anonymous , In Case your system date is US then it will not detect Uk format correctly, refer to this setting

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/599712

or create date like this

new date =
var _pos = search("/",[col],,0)
return
date(right([date],4), mid([date],_pos+1,2), left([date],_pos-1))

[Anonymous]

@amitchandak sorry, it's not about US/UK formatting, it's about puting 00:00:00 at the end of each date in the csv. Any idea?

[Anonymous]

Formatting affects only the appearance of the data piece. Underneath it's just a date which is always a datetime piece and will always have the time bit attached to it. You can't get rid of it. In Power Query, the types you see as selectable are only FACETS of the most basic data types (like datetime) and as such they don't change the representation. So, in a word, you'll always store a date as a datetime. Formatting will never let you get rid of the time piece.