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

[Nimai123]

OLE DB or ODBC error: [DataFormat.Error] We couldn't parse the input provided as a DateTime value..

My data source is SAP BW 

My date column is in text and when i convert it to date it gives me the above error.

I tried splitting and then merging back the column but didn't seem to work out.

Thanks.

@BhaveshPatel 

[amitchandak · Accepted solution]

@Nimai123 , refer is these can help

https://community.powerbi.com/t5/Service/We-couldn-t-parse-the-input-provided-as-a-Date-value/m-p/73...

https://community.powerbi.com/t5/Desktop/DataFormat-Error-We-couldn-t-parse-the-input-provided-as-a-...

Check the exact error on power query

handle error in power query
https://www.youtube.com/watch?v=OE6DPmKqN7s
https://www.youtube.com/watch?v=9-Lag0VOiTs

[amitchandak · Accepted solution]

@Nimai123 , refer is these can help

https://community.powerbi.com/t5/Service/We-couldn-t-parse-the-input-provided-as-a-Date-value/m-p/73...

https://community.powerbi.com/t5/Desktop/DataFormat-Error-We-couldn-t-parse-the-input-provided-as-a-...

Check the exact error on power query

handle error in power query
https://www.youtube.com/watch?v=OE6DPmKqN7s
https://www.youtube.com/watch?v=9-Lag0VOiTs

[Nimai123]

Hello @amitchandak 

Thanks for the informative links.

In my scenario, I cannot replace the error values as I need the rows as the sales data with date and I also tried changing the Locale which also didn't help!

Are there any other options I can try!

Thanks!

[tex628 · Accepted solution]

You should be able to convert text formatted date's to dateTime without any issue so there is something trong with the text. It might be that it's the wrong delimiters or that there are other blankspaces present in the date. If you provide an image of the dates I might be able to see if there are any issues directly. 

Otherwise what you can do if you really can't get it to work is splitting the text column up in year, month and date and use this custom column to create a date: (Make sure that all three columns are formatted to whole number)

#date([Year],[Month],[Date]) as date

Br,
J

[Nimai123]

Hello @tex628 

I have applied the solution which you provided but in my scenario, I cannot create a custom Column as I need to implement Incremental Refresh on the Date column which should be the source Date column and not the Custom Column.

The Date Column looks like below

@amitchandak 

@parry2k 

[tex628]

Is the date you're showing there the 4th of june or the 6th of April?

[Nimai123]

Hello @tex628 

IT is in dd.mm.yyyy format. 

Thanks

[tex628]

Then i believe thats the issue. A text value date in the format xx.xx.xxxx is read as mm.dd.yyyy for me, meaning that every date with a day above 12 will return an error. 

I'm going to be perfectly honest with you, I don't know if it's possible to change this by toggling the locale. Due to the SAP datasource I'm assuming the process of adding another column to the source is a little more than a 30min tweak?

/ J