Forum Discussion

Ximarin's avatar
Ximarin
New Member
3 months ago
Solved

DataFormat.Error: We couldn't parse the input provided as a Date value - Ok, but WHERE?

Hi all,   I import a ton a files. There is one file that has a text value in the date column that causes the query to break. All good, thisis not the problem.   Problem is that I'd want to know...
Need Help
  • MFelix's avatar
    MFelix
    3 months ago

    Hi Ximarin ,

     

    Since Power Query does a truncate of the values and only shows the values for 1000 rows if your files have a lot of data then you won't be able to check the error.

     

    I suggest that before you do the formatting to date you add a new column with the following code:

    try Date.FromText ([YourDateColumn]) otherwise "ERROR"

    This will return values of ERROR on the incorrect ones then filter out the ones with Error and you will be able to check what are those fields, and then handle them has you may see fit.

     

     

     

     

MFelix's avatar
MFelix
Super User
3 months ago

Hi Ximarin ,

 

Since Power Query does a truncate of the values and only shows the values for 1000 rows if your files have a lot of data then you won't be able to check the error.

 

I suggest that before you do the formatting to date you add a new column with the following code:

try Date.FromText ([YourDateColumn]) otherwise "ERROR"

This will return values of ERROR on the incorrect ones then filter out the ones with Error and you will be able to check what are those fields, and then handle them has you may see fit.