Power query is loading dates as date time in spreadsheet

[k2s2]

Hello

I've got columns of data in a power query that are fornatted as dates, as in

"Date Type: Date"

I tried also using the Date button > Date Only 

...but When the Table loads to the worksheet in Excel, It shows as date and time.

How can I make sure it loads the date only?

[v-jingzhang]

Hi @k2s2 

Have you solved this problem? Kindly accept a proper reply as solution or share your own solution if you have got better ones. This would help more people who may have similar questions. Thanks. 

Best Regards,
Community Support Team _ Jing

[timg · Accepted solution]

Hi K2S2,

You could change this in two ways:

1. You can format the values as a "short date" in excel
2. You could explicitly format the date in a format that you would prefer in a calculated column in Power BI. to do this, create a new column with the formatted date. Below you will find an example. When exporting this column it will retain the formatting that you give it

EDIT: @Watsky Pointed out a third great option, which is to format the date as a text value in Power BI

Method 1:

Method 2:

DateFormatted= FORMAT(Table[DateColumn],"dd-mm/yyyy")

Method 3:

Regards,

Tim

[k2s2]

Hi Thanks,  I need for teh dates in Excel to work with averageifs criteria, so text or simply changing the format in excel doesn't work (it just hides the extra data).  

I'll try method 2.  Thanks again