cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Solution Specialist
Solution Specialist

Re: 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)

 

View solution in original post

14 REPLIES 14
Highlighted
Solution Specialist
Solution Specialist

Re: 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)

 

View solution in original post

Highlighted
Frequent Visitor

Re: How to Remove Error in Date Field

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. 

Highlighted
Frequent Visitor

Re: How to Remove Error in Date Field

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!

Solution Specialist
Solution Specialist

Re: How to Remove Error in Date Field

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.

Highlighted
Frequent Visitor

Re: How to Remove Error in Date Field

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?

Highlighted
Solution Specialist
Solution Specialist

Re: How to Remove Error in Date Field

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?

Highlighted
Frequent Visitor

Re: How to Remove Error in Date Field

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.

Highlighted
Solution Specialist
Solution Specialist

Re: How to Remove Error in Date Field

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

Highlighted
New Member

Re: How to Remove Error in Date Field

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?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (740)