Date Format Issue in Power Query mm/dd/yyyy

L

lmk001

New member
Joined
Apr 27, 2020
Messages
5
Reaction score
0
Points
0
Excel Version(s)
OFFICE 365
I have a date field in Power Query where by dates are formatted 04/4/2020 but I would like the date to be formatted as 04/04/2020. I created a custom column that creates 04/04/2020 but it is a text field. I need to convert it back to a date field using the format 04/04/2020 or mm/dd/yyyy. I will attach my file to show you what I did.
 

Attachments

  • DateConverstionProblem.xlsx
    19.5 KB · Views: 11
You need to apply a custom format to the date(s) in the worksheet and not in Power Query.
 
I am in the UK and it is as you want.

Are you in the US, with a en-US locale? In the PQ editor, click on the calendar icon in the date column header. In the drop0down, go to the bottomn and select Using Locale... This will bring up anoither dialoig where you select data type Date, and the Locale of English (United Kingdom), and it should be as you want.​
 
Last edited:
It's still a problem. When I make that change the date is still 04/4/2020. I want it to be 04/04/2020. The data is being pulled in from a csv file so it needs to be transformed in the query editor. Please let me know if you find a solution.
 
I'm in the US and Bob's suggestion worked for me, though I needed to change the column type to text first.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"Date", type date}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Date to Text", each Date.ToText([Date],"MM/dd/yyyy"))
in
    #"Added Custom"

I also tried it with a CSV file, the text conversion isn't needed

Code: 
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"a", type date}}, "en-GB"),
 
You must log in or register to reply here.
Back
Top