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

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
120
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
in PQ editor on Date column use left corner (formats) , choose Using locale... then Date , English - United States
it should work

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

locale.jpg

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
 
Upvote 0
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:

screenshot-10.png


btw. don't quote whole post, use Reply not Reply With Quote (I know my posts), please
 
Last edited:
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top