Forum Discussion
Solved
Excel Powerquery converting incorrect dates
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
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.
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?
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.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.
