Forum Discussion
How to change ordinal data to normal data in Power Query?
Good morning,
Please does anyone know how I can change a date column written like this May 13th, 2007, to a normal date like 13/05/2007 and July 2nd, 2019 to 02/07/2019?
I've already tried using the using locale function in the Power query, but it didn't work.
Please any ideas on how to do this?
Thank you so much
3 Replies
There quite likely is a more elegant way to do this, but I was able to create a formula that removes the "th" or "nd" or "st" from the number of the day, reconstructs as a simpler date in text, and then finds the DATEVALUE of that to produce a standard Excel date. Here's the formula
=DATEVALUE(LEFT(A1,FIND(",",A1)-3)&", "&RIGHT(A1,4))
Fortunately, Excel is smart emough to recognize "May 13, 2007" as a date.
- Hi mathetes, thanks for answering me. The problem is, I need to do this in Power Query as I'm trying to automate the process for a client. I can do this with a lot of substitutions on some columns that will generate a lot of steps in power query. What I'm looking for is a shorter way to replace all these ordinary dates (from text) to date.
The problem is, I need to do this in Power Query as I'm trying to automate the process for a client.
Ahh, well, I'm sorry but although I know of Power Query, it's only recently been available in the Mac ecosystem, and I've not yet had occasion to learn to use it.
