cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
VatsalRaval Helper I
Helper I

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

Accepted Solutions
VatsalRaval Helper I
Helper I

Re: Change the format of the colunm

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

Re: Change the format of the colunm

Hi @VatsalRaval ,

 

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!
Ricardo

VatsalRaval Helper I
Helper I

Re: Change the format of the colunm

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

 

camargos88 Community Champion
Community Champion

Re: Change the format of the colunm

hi @VatsalRaval ,

 

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!
Ricardo

VatsalRaval Helper I
Helper I

Re: Change the format of the colunm

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

 

camargos88 Community Champion
Community Champion

Re: Change the format of the colunm

@VatsalRaval ,

 

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!
Ricardo

VatsalRaval Helper I
Helper I

Re: Change the format of the colunm

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors