I am having some issues with date formatting... it's a nightmare!
So, what I have is a column where I have both dates as "dd/mm/YYYY hh:mm:ss" and others as "dd/mm/YYYY hh:mm:ss AM/PM".
When I change the column format to date/hour I have no issue with the ones that are with the first format but powerquery identifies as error the ones that are with the second format. Some images to ilustrate it:
You should use the new column as you date column, so you must change the format on the column you just created and delete the initial column, then rename the new column to the name of the previous one.
Did I answer your question? Mark my post as a solution!
I can do that but it doesn't solve the problem. The issue I'm having is that with the initial formula it is working properly for the dates ending in PM/AM but it gives me the error message for the other ones.
I have tried to change the last line of the formula, putting instead of Datetime.From Text.Select but it isn't working either.
To sum it up, I think what I need to do is adapt the else clause of the formula that you gave me so the dates in the format dd/mm/yyyy hh:mm:ss are converted to the format dd/mm/yyyy hh:mm:ss PM/AM. And once that is done I will be able to format the new column as date and hour and it will be working properly.
The problem is that I don't know how to change the else clause so it does so...
Do you know what I mean? Any help with the edit of the else clause would be appreciated!