Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sanchit
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
Sean
Community Champion
Community Champion

@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

14 REPLIES 14
nohowUp_fnDown
Advocate I
Advocate I

Why is something as simple as formatting a date so hard in Power BI? Is it that Microsoft is focussing all their development effort on the data engine/ DAX and zero on the development tool? Why do I even have to search "How to format a date in Power BI"? I've been doing this for decades. 

vinayjayappa
New Member

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

 

Thanks

Vinay

Sean
Community Champion
Community Champion

@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

sanchit
Frequent Visitor

@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.

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

Sean
Community Champion
Community Champion

@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

@Sean I will try that . Thanks Smiley Happy

Sean
Community Champion
Community Champion

@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

This worked for me! I guess I'm just confused that in my queries that I'm using as my data source I can pull my dates however I want with: convert(date, cast(sot.closeddate as date), 110)

 

When I pull them into a graph it gets formatted how I think it should, mm/dd/yyyy.

 

But when I pull them into just a plain table, it either comes up as a datetime, or if I change the type in the date column to Date inside the query editor it comes into the table as 'Friday, October 20, 2017' in the table columns.

 

It's a little frustrating that I have to then go through and say 'Hey PowerBI, even though I've already been very explicit with how I want this date formatted in TSQL when I tell you to go out and get the data, I still have to split and remerge my date columns as text fields based on my '/' delimiter.

 

Things that make you go: (/ .□.)\ ︵╰(゜Д゜)╯︵ /(.□. \)

 

This worked for me! I guess I'm just confused that in my queries that I'm using as my data source I can pull my dates however I want with: convert(date, cast(sot.closeddate as date), 110)

 

When I pull them into a graph it gets formatted how I think it should, mm/dd/yyyy.

 

But when I pull them into just a plain table, it either comes up as a datetime, or if I change the type in the date column to Date inside the query editor it comes into the table as 'Friday, October 20, 2017' in the table columns.

 

It's a little frustrating that I have to then go through and say 'Hey PowerBI, even though I've already been very explicit with how I want this date formatted in TSQL when I tell you to go out and get the data, I still have to split and remerge my date columns as text fields based on my '/' delimiter.

 

Things that make you go: (/ .□.)\ ︵╰(゜Д゜)╯︵ /(.□. \)

 

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

Hello,

 

Tried the "Change Type" using "Locale" but I get errors. It seems all the errors happen for the "01", January. Please see below. How to fix this issue?

 

Error message:

 

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
    25/01/2005

You can also alternatively try this as a new column, adjust the sequence of first and second expression depending upon the date format that you have and concat them in the new column.


List.First(List.RemoveFirstN(Splitter.SplitTextByDelimiter("/")([Date]),1)) -> Evaluates second number in the Date value

List.First(Splitter.SplitTextByDelimiter("/")([Date])) -> Evaluates first number in the date value

List.Last(Splitter.SplitTextByDelimiter("/")([Date])) -> Evaluates year in the date value

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

 

Many thanks

Vinay 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.