Results 1 to 5 of 5

Thread: Date Format Issue in Power Query mm/dd/yyyy

  1. #1
    Seeker lmk001's Avatar
    Join Date
    Apr 2020
    Posts
    5
    Articles
    0
    Excel Version
    OFFICE 365

    Date Format Issue in Power Query mm/dd/yyyy



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    120
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    You need to apply a custom format to the date(s) in the worksheet and not in Power Query.

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    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 by Bob Phillips; 2020-04-30 at 05:17 PM.

  4. #4
    Seeker lmk001's Avatar
    Join Date
    Apr 2020
    Posts
    5
    Articles
    0
    Excel Version
    OFFICE 365
    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.

  5. #5
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    120
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    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"),

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •