Forum Discussion
Power query is loading dates as date time in spreadsheet
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?
Hi K2S2,
You could change this in two ways:
- You can format the values as a "short date" in excel
- 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
3 Replies
Hi K2S2,
You could change this in two ways:
- You can format the values as a "short date" in excel
- 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
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
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
