Cleansing Dates - Best Practices

Power Query has the ability to convert this data with your help.  The best practise would be to understand how the data is stored in the source and figure out the most robust method to get that converted into a Date format in Power Query.

 

As your data is going to be a Start Date and End Date, you'll want to have Power Query split this data into 2 columns to eventually store in the Date Format.

 

Once you have the dates in the right columns, now you can concentrate on correcting the data.  One avoidable pitfall will be if you have DD/MM/YYYY data mixed with MM/DD/YYYY data.  You'll have no idea which is which, so you'll have to assume its one or the other for your process.

 

You can attempt to create a new column and make use of Date.From to see if it can convert the Text data for you.  Since this will happen row by row, its possible this might work first time.  Should that fail you could instead add an earlier step of converting the Text under certain circumstances into a staging column.  This might be something like "Are the first 4 characters (Text.Start) a number?  If so, this must be YYYY-MM-DD, i'll then convert that into a standard format (i.e. "DD/MM/YYYY").  You'd only need to write this for the formats that are causing you hassles.

 

Hi Anonymous,

 

As Ross73312 said, you could clean the data in query editor. 

 

If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please share some data sample and your desired output so that we could help further on it.

 

Best Regards,

Cherry