Transform date field

[AleFVG]

in the excel file that I import into power bi there is a date field in the month-year format

example
12019 (january 2019)
122019 (December 2019)
42020 (April 2020)

I have to transform this field into this format: day / month / year

Now I proceed like this

I take the last 4 digits and transform them into the year column
the other two digits proceed like this
if it is equal to 1, then it becomes 1/1 / and I add the year
if it equals 2, then it becomes 1/2 / + year
and so on.

Is there a faster and more effective way?

thank you.

[amitchandak · Accepted solution]

@AleFVG , You can create a date like this

date = date(right([col],4) , left([col], len([col])-4),1)

now you can convert to format from Column tools or format commnad

Text date = format([date], "DD/MM/YYYY")

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[AleFVG]

thanks for the support @amitchandak !

I created a new column, and entered the formula you suggested.

Error displayed below:

Expression.Error: The name "date" was not recognized, please check orthography.

When I enter the formula, it confirms that there are no syntax errors.

[AleFVG]

Sorry @amitchandak !

Your solution works!
I was trying to insert the formula into the data model, and it doesn't work!
I tried to insert it in the table and it works!

many thanks.