DataFormat.Error: We couldn't parse the input provided as a DateTime value.

[Alkhos]

Hello,

I am working with a dataset retrieved as a CSV file through web.

I have a time field on the dataset with values similar to below:

         10/31/2016 4:05:00 PM

When data is fed to PBI desktop, it is automatically detected as "text".

When I attempt to change its type to Date/Time, I get the following error:

       DataFormat.Error: We couldn't parse the input provided as a DateTime value.
       Details:
       10/31/2016 6:25:00 PM

I have checked the source, all values are similarly formatted ( no add values in the time field )

I have also used PromoteHeaders to prmote the first row ( which contains header to the header field ) before trying to change the type.

The exact step that fails uses this DAX code:

         #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"time", type datetime}}),

Is there anything that I am doing wrong? or do I need any further pre-processing on this time field to be able to properly parse it?

Thanks

Ali

[Belin]

If the problem are the leading spaces, as mentioned by @BhaveshPatel , then you need to right click in the column and select transform>trim

https://radacad.com/trim-vs-clean-in-power-bi-and-power-query#:~:text=Trim%20is%20a%20very%20commonl....

If this works better please remeber to mark it, so others will benefit

[MyroslavaM]

You can specify the default format of receiving the data for Date format.
Just add en-GB or en-US in the end in the M code.
it prevents from failing the convertation 🙂 
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}}, "en-GB")

[Anonymous]

Hi, the power query editor considers your laptop's default date format so if the data is not in that format it might cause a problem in changing the data type. 
Hence, you may split the column by delimiter and add a new column (using add a column from example, considering your system's date format).

[danielukasoanya]

I had same error, solution was to check source file and I noticed the bottom cells of the date column was merged and had other data that wasnt dates and could not be formatted as date. Solution was to delete these rows and refresh Power Query and it worked

[maples_90]

I faced the same issue. I went back & checked my data sets, I noticed that the date columns were different in 2 of the 6 files. 

One needs to double check the data source which is being appended. 

[Belin]

data parse is however a more elegant and practical solution as not all the data sources can be manipulated and after a while you may decide to change data source.

Also there is always the problem that if you or anyone else forgot to check the file loaded, the error will reappear...but if you parse, no matter what you always get eh same result...

Think holistically and prepare for the worst 🙂

[Belin]

In Power Query, go to transform in the ribbon and then Date>Parse

[Raj007]

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
7/18/1996

[vaniprasanna]

Thanks. I could resolve the issue. The solution worked.

[IWasTheL10N]

Just a Note:  I had the same error when I tried to format and American Date (MMDDYY).  If you're in Europe (Ireland anyway) you need to arrange the date in DD/MM/YY.  

[RaviTeja_M]

Steps to be followed  to resolve the Date Format Error :

1. When  a CSV file is loaded in Power BI Desktop  by default it detects the 'date/time' field  as 'text' so when we try to convert we get an error.

2. Next Right click on the Field/Column  you want to convert into a Date Field and navigate to 'Change Type' .

3. Next Click on 'Using Locale' option under the 'Change Type' dropdown.

4. Change the 'Data type' to 'Date' in the dialog box of 'Using Locale'  as by default it takes it as 'text'.

5. Click on the drop down of 'locale'.

6. Then Select an 'Option' from drop down for which your Date field  'Format' matches exactly and Click on 'OK' . 

7. Above steps will resolve the issue and same solution goes well for Date/Time column conversion also.

[PatChan]

This is awesome detailed solution!

[HelgiLaxdal]

Thank you! 

[Rushikesh_2022]

These steps were really helpful. thanks

[AbbasG]

@RaviTeja_M this helped me solve the problem thank you.

[Pala]

Thank you! This solution worked for me 😀

[sreekumar_G]

this solution worked for me, thanks a lot for sharing your knowledge

[vrushabjain510]

Superb! Works perfectly

[mch]

@RaviTeja_M your hint solved my issue, thak you!

[kingrob01]

How would I fix this error? It appears after I perform a data refresh.

DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
18 Days 15 Hours 59 Minutes