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
Anonymous
Not applicable

Change the format of the colunm

Hello Power BI Community,

 

I have a date column with the format 20200304 for 3rd April 2020. I want to change it to the 4/3/2020 12:00:00 AM format. I tried changing the data type date/time but isn't helping. Please provide some guidance. Thanks. 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

It is solved. I went to the query editor and then I could change the data type and date format to the required one.

View solution in original post

6 REPLIES 6
camargos88
Community Champion
Community Champion

Hi @Anonymous ,

 

Try this code for a new calculated column:

 

FormatDate = FORMAT('Table'[date]; "d") & IF(DAY('Table'[date]) = 1; "st"; IF(DAY('Table'[date]) = 2; "nd"; IF(DAY('Table'[date]) = 3; "rd"; "th"))) & " " & FORMAT('Table'[date]; "mmm YYYY")
 
Ricardo


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

@camargos88 It did not work. It is giving me the following error. "Cannot convert value '20200405' of type Text to type Date."

 

hi @Anonymous ,

 

I got the same error, so I had to apply a trim and clean on Power Query, before create a calculated column.

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

@camargos88 Can you explain a bit more about trimming and cleaning for this problem? I am new and still learning. Thanks. 

 

@Anonymous ,

 

TRIM -> Returns the result of removing all leading and trailing whitespace from text value text.

CLEAN -> Returns a text value with all non-printable characters of text removed.

 

https://docs.microsoft.com/en-us/powerquery-m/understanding-power-query-m-functions

 

Check this file as an example: Download PBIX 

 

Go to Transform Data and check the steps applied.

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

It is solved. I went to the query editor and then I could change the data type and date format to the required one.

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.