Forum Discussion
How to Remove Error in Date Field
Hello, I've been searching the community but have not found luck in addressing an issue I am having. I am importing data through Salesforce's Report capability. Of the many rows being processed, only 3 rows have an issue with a specific date field that contans no date and time stamp (mm/dd/yyyy hh:mm:ss AM or PM)
I'd like to know how to add a process or task that analyzes this date column each time a refresh is performed so that when it detects the blank fields, it auto-populates them with some insignificant value. Like 11/11/2011 11:11:11 AM or is there another best practice? I don't care about having 'bad' data that is hard coded in this unique case becasue I would know how to spot it.
Any help or suggestions are appreicated.
Thanks
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)
16 Replies
am getting this error as well. power bi does not put null for blank cells if we format the column as date. is there a way to work around this?
It actually just let me right-click in the power query and select replace errors and I typed 'null' and it worked
Ah, thank you. This fixed my problem.
drewlewis15Solution Specialist
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)
Thanks Drew. For this case I was fine with the hard coded date setting and did successfully add it using the replace action to the query for that specific column.
As a follow up question I need the query to not error out on blank date entries. Blank date entries are valid as I'm looking at ticket history and am scaning tickets that are not yet 'closed'. I am going to use the closed ticket date field as a filter in order to display what tickets were closed during that period.
What's the best way to get around having a blank closed date value that is causing errors in my query for any record where the closed date is blank?
Thanks!
- drewlewis15
Could you send me an example of the error and the field causing the error. I am not sure I understand what you are trying to do with the field. If you are trying to write a formula to do something to a date but blank dates are throwing out errors, then use a try statement where you would write something like this:
try Date.FromText([Date]} otherwise (How you want to handle your error) This will basically try to do whatever you are wanting to do to the date field, and if it can't because it is blank, it will populate with whatever (null, "Text", etc.) But again, I might be misunderstanding your question. A screenshot would help if possible.
am getting this error as well. power bi does not put null for blank cells if we format the column as date. is there a way to work around this?
Hi Guys, Im a newbie in regards to working with Power Bi. I have not uderstand what was the solution for this error? Can some one explain? In query editior I have column with dates for Project End Date few project required to be update with To Be Confirm (TBC) text - how to keep this with no error? Is there a posibility to do that?
