Different Date format- dd/mm/YYYY and dd.mm.YYYY

[surajbh]

I have different date format ( dd.mm.YYYY and dd-mm-YYYY ) in the source file at the same column- Date, and would like to convert those different format into a single format of Date on powerquery. 

Looking for the best and easily solution. 

Thank you in advance for the support. 

😊

Regards, 

Suraj 

[Einomi]

Hello,

I am not sure where the 12/31/9999 comes from ? can you explain

[surajbh]

this is coming from one of the source file. 
i do not have the edit right on the source file.

[Einomi]

Hi, can you share a screenshot of the source file and the expected outcome

From what you wrote, why cannot you just replace the values all the . by / and all the - by / ?

[surajbh]

the source is an excel file, and i would like to bring those two dates format into 1 same format.

[ronrsnfld]

In your Excel file, the left-justified strings that look like dates are actually text strings and not "real dates".  Because of how Excel stores and parses dates, I suspect that the dates are coming from a CSV or text file that was OPEN'd; the original dates are in MDY format, but your computers regional settings call for dates to be in a DMY format. If that is the case, I suggest you IMPORT the csv file directly into Power Query, and set the date format by locale, using something like English-US for the date column.

Note that if my assumptions are correct, even the valid dates will have been converted incorrectly.

[surajbh · Accepted solution]

i could manage with further google, to change the setting of power bi- now it works. 
thanks for the direction- was helpful,

Thanks.

[surajbh]

If i convert the column into date format- it will show

This is the error message, i get when i click on Error-

Hope this helps. 

Thank you.