cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thoco
Frequent Visitor

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

1 ACCEPTED SOLUTION
drewlewis15
Solution Specialist
Solution 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)

 

View solution in original post

16 REPLIES 16
gaba2018
New Member

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? 

meihuaua
New Member

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?

meihuaua
New Member

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.

drewlewis15
Solution Specialist
Solution 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)

 

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
Solution Specialist
Solution Specialist

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.

2018-02-08 17_00_16-Untitled - Query Editor.png 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?

drewlewis15
Solution Specialist
Solution Specialist

If 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.

Anonymous
Not applicable

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.

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?

Anonymous
Not applicable

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.

drewlewis15
Solution Specialist
Solution Specialist

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... 

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. 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors