Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
surajbh
Helper I
Helper I

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

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 

 

1 ACCEPTED SOLUTION

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

surajbh_0-1678432506697.png

 

Thanks.

 

View solution in original post

7 REPLIES 7
Einomi
Resolver II
Resolver II

Hello,

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

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

Einomi
Resolver II
Resolver II

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 / ?

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

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.

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

surajbh_0-1678432506697.png

 

Thanks.

 

 

surajbh_1-1678106502329.png

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

surajbh_2-1678106587532.png

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

surajbh_3-1678106627812.png

Hope this helps. 

Thank you. 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors