Aug 02 2022 01:17 AM
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
Aug 02 2022 02:04 AM
@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?
Aug 02 2022 02:07 AM - edited Aug 02 2022 02:09 AM
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.
Aug 02 2022 02:14 AM
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.
Aug 02 2022 02:44 AM
Aug 02 2022 02:14 AM
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.