Data source errorNot a legal OleAut date.. The exception was raised by the IDataReader interface. Pl

[akhaliq7]

In my power bi service I am getting an error while doing a data refresh. I checked other posts on the same issue and the solutions that were given were that it might be a date value such as with the year 1900 in your dataset. Now the problem is that how do i find such a value as i have 246 columns and 16000+ rows. Also i am able to get the data from power bi desktop just in power bi service i get this error. Is there a search facility or a automated way of getting the value that is out of bounds.

[akhaliq7 · Accepted solution]

Update: A even better solution I found to this problem was:

1. Select choose columns then unselect all columns.  

2. Type in date, (only works if date is in the column name). Select all the search result columns.

3.1.  Select column profile under view tab, find what columns you have the wrong values in should appear as the min value in column profile.

3.2. You can also sort ascending to a date column and remove nulls. This will show you all the year values that start with 0. If you work in a company need to take all the rows and give it to your senior team member. I copied the table and pasted the values to a excel file.

4. Take a note of the column(s) that have the wrong date value in e.g. 0202.

5. Clear above steps taken so far in applied steps,

6. Select Go to column, then type the column name,

7. Then use a date filter (after). I used after 01/01/1900 or equal to null

and that fixed my problem in minutes compared to the way i tried to fix it yesterday which took my a lot longer to fix