Forum Discussion

k2s2's avatar
k2s2
Frequent Visitor
4 years ago
Solved

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 works...
Need Help
  • timg's avatar
    timg
    4 years ago

    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

timg's avatar
timg
Solution Sage
4 years ago

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's avatar
    k2s2
    Frequent Visitor
    4 years ago

    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