• 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
 
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.
 
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.
 
Post a sample file that we can work with to provide a solution.
Please find the attached sample dates errors - its visible as formatted date but in the formula bar showing the old format.. cant use the column
 

Attachments

  • sample file.xlsx
    26.1 KB · Views: 2
Like @Chihiro and myself suggested. Delete the Changed Type step and use locale (working on my end).
 

Attachments

  • Copy of sample file_PQ_Dates.xlsx
    52.7 KB · Views: 0
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

  • sample file.xlsx
    27 KB · Views: 1
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?
 
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...
 
Back
Top