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

[JamesonMH]

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

 

[sandy666]

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

 

[JamesonMH]

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

 

[sandy666]

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

 

[JamesonMH]

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

 

[sandy666]

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