Power Query Custom Column Date Conversion

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Power Query Custom Column Date Conversion

Post by jstevens »

I'm having a challenge with a Power Query Custom Column converting a text string field ("27/08/2020") which is in the format of dd/mm/yyyy to mm/dd/yyyy. Lets call the text string field [UKDate] and the new custom column [USDate] as date not text.

I tried =format([UKDate],'"dd/MM/yy") but that did not work. Remember the [UKDate] is a string field not date.

Your thoughts are appreciated.
Regards,
John

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Power Query Custom Column Date Conversion

Post by jstevens »

I was able to resolve the challenge by:

1. Running a "split column" on [UKDate] based on a delimiter of "/". This step created three new columns: dd, mm and yy.
2. Rearranged (moved) the new columns to mm, dd and yy
3. Merged the three columns using a delimiter of "/"
4. Step 3 created a new column in mm/dd/yy format
Regards,
John

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Power Query Custom Column Date Conversion

Post by HansV »

Congrats! :thumbup:

Thanks for sharing the solution.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Power Query Custom Column Date Conversion

Post by Rudi »

:thumbup:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Power Query Custom Column Date Conversion

Post by rory »

You should also be able to use: Date.FromText([UKDate],"en-gb")
Regards,
Rory