power query advanced editor using m-code transforms dates incorrectely
Results 1 to 6 of 6

Thread: power query advanced editor using m-code transforms dates incorrectely
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default power query advanced editor using m-code transforms dates incorrectely

    I have the following code that is not keeping the same date format as the input. The input dates are in a date column and have mm/dd/yyyy formatting in the cell (e. g. 5/7/2019). The result after running the following m-code is 07/05/2019 in a general format. How can I get the output dates to be the same as the input dates using the following code?


    let
    Source = Excel.Workbook(File.Contents("C:\Users\Sadie\MyDocuments\Excel\ExcelPowerBuilder\pb-v3-test.xlsm"), null, true),
    Schedule_Sheet = Source{[Item="Schedule",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Schedule_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Time", type datetime}, {"Courts", Int64.Type}, {"Teams", type text}})
    in
    #"Changed Type"

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,883
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: power query advanced editor using m-code transforms dates incorrectely

    Have you checked your regional settings to ensure that they are what you want them to be.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    New Member
    Join Date
    Sep 2008
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: power query advanced editor using m-code transforms dates incorrectely

    Yes, have went to windows 10 change date, tome or number formats and see the following.
    Region is set to 'match windows display language'
    short date is set to M/d/yyyy
    example shows 7/26/2019

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: power query advanced editor using m-code transforms dates incorrectely

    also check Regional Settings in PQ Editor
    File - Options and Settings - Query Options - Regional Settings - Locale (set to English (United States)
    Last edited by sandy666; Jul 26th, 2019 at 01:52 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    New Member
    Join Date
    Sep 2008
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: power query advanced editor using m-code transforms dates incorrectely

    OMG OMG .

    I was using GB english instead of US english and they have dd/mm/yy and the US has mm/dd/yy. Such as simple solution, for me to overlook. But, the solution is always simple when someone else solves it for you.

    My many thanks and appreciation for your excellent help. YOU ROCK !!!!

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: power query advanced editor using m-code transforms dates incorrectely

    You are welcome

    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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