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 want the column to be of type date/time. The empty entries in this case are marked as Error. I need a way to retain all of the information and cannot 'remove errors'. Besides replacing Error with a hardcoded date, what are my options?
Solution SpecialistIf the cells are empty, then it should be marking them as null instead of an error... Can you should me the data within the cells prior to the error along with the step that is causing the error?
The cells are blank, empty. When they are initially processed it is in a text format. I then convert it to date/time and get the erros on the blank dates.
- drewlewis15
Do you have some sample data? I am not able to replicate your issue... When I try loading a table of dates into Power BI with blank cells within the table, I am not receiving an error when converting them to a date format. I might just be misunderstanding the issue...
Did you ever figure this out? I am running into the same issue with needing blank cells in a date field. It errors out when I convert to a date.
- drewlewis15
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?