How to convert dd.mm.yyyy to dd/mm/yyyy?

[Anonymous]

Hello,

I have a column of dates imported from an Excel sheet into Power Query within Power BI. The column has dates in two different formats: dd.mm.yyyy and dd/mm/yyyy. When changing the column type from text to date, it only recognizes the dates in the format dd/mm/yyyy but for the dates in the format dd.mm.yyyy it just shows error. How to convert all the dates in the correct format dd/mm/yyyy without errors? Any help is much appreciated!

Changing type to Date:

Source data from Excel:

Day

03.01.2021

03.01.2021

04.01.2021

04/01/2021

04/01/2021

04/01/2021

05/01/2021

05/01/2021

08/01/2021

08/01/2021

08/01/2021

08/01/2021

08/01/2021

08/01/2021

10/01/2021

10/01/2021

[Vijay_A_Verma]

if your default date format of PQ is dd/mm/yyyy then it will automatically give the right output when you selecte Date data type.

Otherwise, you can use Locale English(UK) to convert this into dd/mm/yyyy column. You will need to click ABC123 in your column heading - Using locale - Data type, select Date - Locale - English (UK)

If above doesn't work for you, replace . with / by selecting column - Transform - Replace values - Value to find will be . and Replace with will be /

Then you can convert the column to Date.

OR

You can use following formula in a custom column

=Date.From([Day])

OR

=try Date.From(Text.Replace([Day],".","/")) otherwise Date.From([Day])

[Anonymous · Accepted solution]

I changed the Date - Locale - English (UK) for the column in PQ but it didn't work. However, i changed the Regional settings and it worked!