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

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
106
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:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,328
in PQ editor on Date column use left corner (formats) , choose Using locale... then Date , English - United States
it should work

 
Last edited:

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
106
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,328
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 :LOL:



btw. don't quote whole post, use Reply not Reply With Quote (I know my posts), please
 
Last edited:

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
106
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:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,328
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,137
Messages
5,466,893
Members
406,507
Latest member
donwiss

This Week's Hot Topics

Top