SOLVED

Excel Powerquery converting incorrect dates

New Contributor

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.

Adityaa46_0-1659428195315.png

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

 

Any suggestions are highly appreciated

4 Replies

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

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.

best response confirmed by Adityaa46 (New Contributor)
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.

It works
Thanks for catching the error