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.
Power Query Custom Column Date Conversion
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Power Query Custom Column Date Conversion
Regards,
John
John
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Power Query Custom Column Date Conversion
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
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
John
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Power Query Custom Column Date Conversion
Congrats!
Thanks for sharing the solution.
Thanks for sharing the solution.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Power Query Custom Column Date Conversion
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 817
- Joined: 24 Jan 2010, 15:56
Re: Power Query Custom Column Date Conversion
You should also be able to use: Date.FromText([UKDate],"en-gb")
Regards,
Rory
Rory