Excel Powerquery converting incorrect dates

[Adityaa46]

Hi All,

I am extracting dates from a text column and converting them into dates.

I have Some dates getting corrrectly parsed but however some dates are getting incorrectly parsed.

I am using Column C to extract the dates. But however as higlighted in yellow it doesnt work properly.

Any suggestions are highly appreciated

[#2]

@Adityaa46 Why do you only highlight those two? There seem to be others that are inconsistent as well?

What exactly did you do in PQ to split the text column?

[#3]

Hi @Riny_van_Eekelen 

I just marked them as an example of the error. I am aware that the dates are wrongly formatted in PQ.
Steps which i took to extract dates from C column
1. Extract last 7 letters from the text and add it in a new column
2. Change its data type to Date.

[#4 · Accepted solution]

@Adityaa46 Then I suspect that some texts have trailing spaces. For example, if the text ends with "...Final_25Jan21 " and you take the last 7 characters, PQ returns "5Jan21 ". Use the Trim option on the text column before you split it.

[#5]

It works :)
Thanks for catching the error :)