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.

     

     

     

     

pcoley's avatar
pcoley
Super User
3 months ago

Ximarin 
In Power BI (specifically within the Power Query M language), the try...otherwise construct is the primary method for handling errors during data transformation. It functions similarly to the IFERROR function in Excel, allowing you to define a fallback value if a calculation or step fails.
The basic sintax is : try [Expression] otherwise [FallbackValue]
Therefore you can try to handle Conversion Errors: Useful when converting to dates where some cells may contain non-date data. (Taking into account the solution given by MFelix) Try to convert all the columns that are supposed to have date types using a [FallbackValue] that returns a fixed rare date (a date; not a text like "error"); then you can go to the data loaded into powerbi and filter the data that have that rare date; for example 1jan1900:

try Date.From([YourDateColumn]) otherwise #date(1900, 1, 1)