• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Query - Date column identified as Date - Error "DataFormat.Error: We couldn't parse the input provided as a Date value"

BBH

New Member
Hi!.. While loading the data file, the date column which is in dd/mm/yyyy format ( different from my system settings -mm/dd/yyyy) is detected as date but error comes up while loading.. one example of that error is as follows.. using locale - changing of the format from English (US) to English ( Australia) also didnt work.. plz help out on this error.. thnx

Sales Date
13/07/2021
 

vletm

Excel Ninja
BBH
If You're asking Power Query-question,
then You should have posted it to Power BI, Power Query and Power Pivot-Forum.
As You've read from Forum Rules, before the first post.
... please, reread it.
Your thread is moved there.
 

Chihiro

Excel Ninja
When importing data, replace the current "Changed Type" step with your own.

Select "Changed Type" step in Applied steps pane. Then in column click on data type icon. At bottom you should see "Using Locale...".

Set data type to "Date" and use Locale English (United Kingdom). For source date formatted dd/mm/yyyy.
 

BBH

New Member
When importing data, replace the current "Changed Type" step with your own.

Select "Changed Type" step in Applied steps pane. Then in column click on data type icon. At bottom you should see "Using Locale...".

Set data type to "Date" and use Locale English (United Kingdom). For source date formatted dd/mm/yyyy.
Hi!.. plz look at the attached sample file.. after using locale option , the view is required format ie.. English (US) but in the formula bar it is still showing as English (UK) format only.. unable to use this column
 

Attachments

GraH - Guido

Well-Known Member
Hi!.. plz look at the attached sample file.. after using locale option , the view is required format ie.. English (US) but in the formula bar it is still showing as English (UK) format only.. unable to use this column
So the error is not inside PQ, but in Excel?
I don't have an issue with these dates in PQ nor in Excel (using Local EN-AUS)

EDIT:
Ok thank you...
Confused now, it's working then?
 

BBH

New Member
So the error is not inside PQ, but in Excel?
I don't have an issue with these dates in PQ nor in Excel (using Local EN-AUS)

EDIT:

Confused now, it's working then?
Hi!. the original file was exceeding 5 MB so couldnt upload the same.. pasted the same in excel..

Changing the LOCALE in QUERY SETTINGS seems to be working for me.. instead of changing the locale in steps..in query..working.. TRIED IT OUR JUST NOW... thank you so much for your help...
 
Top