How to Correct Different Format Dates in a Column via POWER QUERY?

[RahimZulfiqar87]

How to Correct Different Format Dates in a Column via POWER QUERY?

Data

DatesCorrection

6/1/2023	6/1/2023
6/2/2023	6/2/2023
6/3/2023	6/3/2023
6/4/2023	6/4/2023
6.5.2023	6/5/2023
6.6.2023	6/6/2023
6.7.2023	6/7/2023
2023-27-6	6/27/2023
27-6-2023	6/27/2023
31052022	5/31/2022
6..20…2023	6/20/2023

[collinsg]

Good day @RahimZulfiqar87 ,

Loading the dates into Power Query and setting the column type as "Date" will make a best effort guess at the format but is not guaranteed. For example text of 6/12/2023 (d/M/yyyy) and text of 12/6/2023 (M/d/yyyy) are the same date but Power Query will convert them using its Culture (or Culture you specifiy) and so convert them to different dates.

For robust conversion you need to tell Power Query the format it is ingesting and the locale you want translated to. Provided you do this you will be successful converting any of your formats. For example, if you load your dates you can use Date.FromText. It has optional parameters where you specify the format being ingested and the culture to convert to, e.g. with a column of "type" ABC123 called "Dates",

Date.FromText( [Dates], [Format="M..d...yyyy",Culture="en-US"] )
or
Date.FromText( [Dates], [Format="d-M-yyyy",Culture="en-US"])

For the case of 31052022 Power Query will see it as a number so you could

Date.FromText( Text.From([Dates]), [Format="dMMyyyy",Culture="en-US"])

Date.FromText is described here and the format codes are described here.

Hope this helps