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

[BBH]

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]

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.

 

[AlanSidman]

Post a sample file that we can work with to provide a solution.

 

[GraH - Guido]

Remove the step where PQ detects data types. Then use the locale one. Dos that help?

 

[Chihiro]

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]

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

 

[GraH - Guido]

Like @Chihiro and myself suggested. Delete the Changed Type step and use locale (working on my end).

 

[BBH]

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

 

[BBH]

Like @Chihiro and myself suggested. Delete the Changed Type step and use locale (working on my end).

Ok thank you...

 

[GraH - Guido]

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]

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...