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

[Ximarin]

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 WHAT file is causing this.

None of the files error out when the custom function is applied.

It's when I actually load the data that the error stops the query and I can't figure out the file causing this.

I only get the nasty yellow error message, no further details except for the string that is the non-date-value.

How would I find the file causing this?

Thanks,  Tobias

[v-aatheeque]

Hi @Ximarin 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[v-aatheeque]

Hi 

We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.

[pcoley]

@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)

[pcoley]

@ Ximarin, 
In powerquery: Please try changing the column profile (selecting to preview the steps with all the dataset and not only the top 1000 rows) so you can see what happens with all data in the powerquery canvas before loading the data to power bi; therefore i expect you can check where the error is.

[Protype83]

Hey, 

What is format of those files? 

If it is text/csv file then there might be just delimiter in wrong position. 

Tab delimiter can be hard to spot. 

[MFelix · Accepted solution]

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.