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

14 REPLIES 14
Highlighted
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?

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

Highlighted

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?

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

Highlighted

It actually just let me right-click in the power query and select replace errors and I typed 'null' and it worked

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

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors