cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sanchit Frequent Visitor
Frequent Visitor

How to change the date format?

Hi,

For Data: I have imported a .CSV file.

I have two questions?

  1. My Date is in format 1/3/2016 7:56:00 AM (DD/MM/YYYY hh:mm:ss), but PowerBI is reading the Date as MM/DD/YYYY hh:mm:ss.

         How can I change the date format to the desired format?

     2.Another column has date in DD/MM/YYYY (27/08/2016) format but PowerBI is reading it as Text.

  •  While trying to convert it into required format, from Modelling->Data Type in the Report Window, it gives an error “We can’t automatically convert the column to Data Type.”
  • While trying to convert it from Edit Queries -> Transform, output is Error in each cell.

        Some cells have blank data.

This is my first question, please pardon me for the language.

Thank you for the help.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Sean Super Contributor
Super Contributor

Re: How to change the date format?

@sanchit This is controlled by the Locale Setting!

 

MM/DD/YYYY is the US format (and maybe some other countires not sure???)

 

But your csv file likely came form a place where they use DD/MM/YYYY

 

To have PBI recognize those as dates Go to File => Options and settings =>  Options => Locale => change to your country/region

 

Date Errors3.png

View solution in original post

12 REPLIES 12
Highlighted
Sean Super Contributor
Super Contributor

Re: How to change the date format?

@sanchit This is controlled by the Locale Setting!

 

MM/DD/YYYY is the US format (and maybe some other countires not sure???)

 

But your csv file likely came form a place where they use DD/MM/YYYY

 

To have PBI recognize those as dates Go to File => Options and settings =>  Options => Locale => change to your country/region

 

Date Errors3.png

View solution in original post

sanchit Frequent Visitor
Frequent Visitor

Re: How to change the date format?

@Sean Thank you so much Smiley Very Happy It worked. 

I was trying it with DAX but in vain , is there a way to solve it using DAX , manually adding a new column with a different date format.

Sean Super Contributor
Super Contributor

Re: How to change the date format?

@sanchit Probably easiest way would be in the Query Editor

 

Split Column => by delimiter => Custom "/" at each occurence

 

And then Merge those columns in the "correct" order.

 

That should work!

sanchit Frequent Visitor
Frequent Visitor

Re: How to change the date format?

@Sean I will try that . Thanks Smiley Happy

Sean Super Contributor
Super Contributor

Re: How to change the date format?

@sanchit Here are the steps to convert DD/MM/YYYY to MM/DD/YYYY

 

Date Format Conversion 1.png

 

Date Format Conversion 2.png

 

Date Format Conversion 3.png

 

Date Format Conversion 4.png

baptiste Frequent Visitor
Frequent Visitor

Re: How to change the date format?

Another way of doing:

 

In Power Query Editor, right-click on the column header / "Change Type" / "Using Locale..." / then choose Date format with the locale you want

Capture.PNG

source:

https://community.powerbi.com/t5/Desktop/date-format-is-changed-in-power-bi-desktop/m-p/45385#M17618

geejaysantiago Frequent Visitor
Frequent Visitor

Re: How to change the date format?

hi @sanchit what locale setting did you use? i need the dd/mm/yyyy option as well

vinayjayappa Frequent Visitor
Frequent Visitor

Re: How to change the date format?

How do we change only the column header to date format and the data to be in the number format?

 

Many thanks

Vinay 

vinayjayappa Frequent Visitor
Frequent Visitor

Re: How to change the date format?

How do we change the format of the column header to date format and remaining data to be in number format?

 

Thanks

Vinay

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 428 members 4,220 guests
Please welcome our newest community members: