Change the format of the colunm

[Anonymous]

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. 

[camargos88]

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

[Anonymous]

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

[camargos88]

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

[Anonymous]

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

[camargos88]

@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

[Anonymous · Accepted solution]

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