Date Format Issue in Power Query mm/dd/yyyy

[lmk001]

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.

 

[NormS]

You need to apply a custom format to the date(s) in the worksheet and not in Power Query.

 

[Bob Phillips]

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.​

 

[lmk001]

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.

 

[NormS]

I'm in the US and Bob's suggestion worked for me, though I needed to change the column type to text first.

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

    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"a", type date}}, "en-GB"),