Forum Discussion
How to Remove Error in Date Field
If you are wanting to change all null values to a specific date (like 11/11/2011), then a simple replace values would work where you are replacing all null values with your ficticious date. You can also add a custom column with the following statement:
if [Your Date Column] = null then "11/11/2011" else [Your Date Column]
If you are trying to convert everything to a date and are receiving an error message, you can also try the following statement in a custom column (if your date column is currently text because of bad data):
try Date.FromText([Your Date Column]) otherwise (however you want the errors handled)
I am still not able to replicate the issue... When I have dates formatted as text and then converted to a date, all blank cells are shown as "null" rather than errors. I even tried adding spaces instead of having truly blank cells, but the query editor handled it in the same way. Is there some test data that you can share to help duplicate the issue?
I finally figured out my issue. I thought it was brining in blanks when I looked at the data but it was actually bringing in "Unknown". I converted the "Unknown" to "null" and then changed the type to "date" and it worked. Support was able to help me with this. I believe I was looking at the data after I tried converting to a date instead of prior (which is why I saw blank instead of unknown) AND I forgot to "load more.." results to see in the drop down for the column what it was bringing in besides dates prior to trying to convert to a date.