Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Power Query - how to get imported date (m/dd/yy) recognized as a date

  1. #1
    Board Regular
    Join Date
    Apr 2018
    Location
    Ontario, Canada
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query - how to get imported date (m/dd/yy) recognized as a date


    Power Query - how to get imported date (m/dd/yy) recognized as a date
    Hi all,

    I import GL transaction data in CSV form and the one error I can't fix in PQ is the date column (e.g. 4/30/19 which PQ won't accept as a date). That's the format out of the GL.

    When I do a simple Replace ("/19" to "/2019") it makes it a recognized date for most rows, but some dates are say 4/19/19 which creates an error (4/2019/2019).

    Please help if you can - it's the broken link that's stopping me from finishing my project.

    This is my first post in the Power BI section - if it's half as good as the main forum I'll be in luck. Thanks folks!

    James
    Last edited by JamesonMH; Apr 18th, 2019 at 07:23 PM.

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

    Cool Re: Power Query - how to get imported date (m/dd/yy) recognized as a date

    in PQ editor on Date column use left corner (formats) , choose Using locale... then Date , English - United States
    it should work

    Last edited by sandy666; Apr 18th, 2019 at 08:03 PM.
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Apr 2018
    Location
    Ontario, Canada
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query - how to get imported date (m/dd/yy) recognized as a date

    Quote Originally Posted by sandy666 View Post
    in PQ editor on Date column use left corner (formats) , choose Using locale... then Date , English - United States
    it should work

    Thanks for your reply sandy666, I appreciate you including the screen pics too. I just tried to quickly recreate my problem in a CSV here at home (the original file is on my work computer) and regardless how I try to mimic it, PQ correctly recognizes it as a date once it's imported (go figure) - maybe it's a sign I should be working from home? : )

    So basically, I'm going to be waiting until Monday when I'm back at my desk and can try your solution.

    I live in Canada and actually used that locale option to choose English (Canada) earlier today, and it turned everything to errrors. Now I'm confident the USA option will be the answer. I'll update this thread on Monday night, thanks again!

    James

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

    Cool Re: Power Query - how to get imported date (m/dd/yy) recognized as a date

    I use dd/mm/yyyy (UK windows locale) but if I see something like m/d/yy (US locale or any other with this format) I set just US locale and PQ "translate" it to my Windows local settings, in your case it will be English Canada (probably).

    just for fun



    btw. don't quote whole post, use Reply not Reply With Quote (I know my posts), please
    Last edited by sandy666; Apr 18th, 2019 at 10:26 PM.
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular
    Join Date
    Apr 2018
    Location
    Ontario, Canada
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Power Query - how to get imported date (m/dd/yy) recognized as a date

    I tried out your solution today at the office and it worked perfectly! All of the errors are gone and I was able to finish my project as a result. Thanks again for your help, very much appreciated.


    James
    Last edited by JamesonMH; Apr 22nd, 2019 at 08:35 PM.

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

    Default Re: Power Query - how to get imported date (m/dd/yy) recognized as a date

    I am glad your problem is solved

    don't forget to hit Thanks/Like button in the post which helped you

    have a nice day
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    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
  •