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 

[Anonymous]

Hi, I think this Global regional settings will work only for import mode but mine is direct query mode. My issue is date is in text format in dd-mm-yyyy format in tabular cube and in power bi report it is showing as dd-mm-yyyy format but when i export to excel few dates are displayed as dd-mm-yyyy format and few in dd/mm/yyyy format, can someone please help?

[ronrsnfld]

Excel is interpreting some of the dates as real dates and others as text strings. This is usually due to a mismatch between the date format and your Excel windows date settings. During the import process, you will need to tell excel the format of the incoming dates, BEFORE it converts them.

How exactly are you exporting to Excel? If it is a CSV file, then IMPORT it into Excel using either Power Query or the Legacy Import Wizard, and be sure to tell the legacy import tool that your incoming csv has dates in DMY format (or use the optional culture argument in the step in Power Query where you first set the data types).

[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.